DURATION PROBLEM Hi! I'm having trouble with...

(Matheus Trilico) #1

DURATION PROBLEM

Hi! I’m having trouble with DURATION! See below an example:

Column A [START DATE] Column B [START TIME] Column C [END DATE] Column D [END TIME] Column E [DURATION]: END TIME - START TIME.

However, when start and end DATES are different, the duration value doesn’t work because it calculate only based on hours. For example:

[START DATE]= 02/21/18 [START TIME]= 22:00 [END DATE]=22/02/18 [END TIME]= 01:00 [DURATION]= -21:00 (It should be 03:00 - from 22:00 day 02/22 to 01:00 day 02/22)

How can I solve that?

Thanks a lot!

(James W Rezin) #2

If you are getting the date in one column and the time in another, try creating a VC that concaternates them into DateTime column type. If Smartsheet is backend it will not work as Smartsheet does not do DateTime except in System fields.

Google sheets should work fine.

(Matheus Trilico) #3

@James_W_Rezin I tried doing what you said like this: VC 1 [START] what was the formula to concatenate [start date], [start time].

VC 2 [END] what was the formula to concatenate [emd date], [end time].

Then I tried to put a formula to calculate duration in the colunm [DURATION] like this: [START] - [END] using both VCs. But the answer was: "Arithmetic expression ([START]-[END]) has inputs of an invalid type "

I need DURATION that result in hours and be able to calculate with different days and more than 24h duration…

(Aleksi Alkio) #4

Try with… ([END DATE]+([END TIME] - “00:00:00”)) - ([START DATE]+([START TIME] - “00:00:00”))

(James W Rezin) #5

@Matheus_Trilico [Starting] =Concatenate([Start Date],",",[Start Time]) [Ending] =Concatenate([End Date],",",[End Time]) [Duration] =[Ending]-[Starting]

(Matheus Trilico) #6

@Aleksi_Alkio

Amazing! It works!!! Thank you again!

(James W Rezin) #7

If you are getting the date in one column and the time in another, try creating a VC that concaternates them into DateTime column type. If Smartsheet is backend it will not work as Smartsheet does not do DateTime except in System fields.

Google sheets should work fine.

(Matheus Trilico) #8

@James_W_Rezin I tried doing what you said like this: VC 1 [START] what was the formula to concatenate [start date], [start time].

VC 2 [END] what was the formula to concatenate [emd date], [end time].

Then I tried to put a formula to calculate duration in the colunm [DURATION] like this: [START] - [END] using both VCs. But the answer was: "Arithmetic expression ([START]-[END]) has inputs of an invalid type "

I need DURATION that result in hours and be able to calculate with different days and more than 24h duration…

(Aleksi Alkio) #9

Try with… ([END DATE]+([END TIME] - “00:00:00”)) - ([START DATE]+([START TIME] - “00:00:00”))

(James W Rezin) #10

@Matheus_Trilico [Starting] =Concatenate([Start Date],",",[Start Time]) [Ending] =Concatenate([End Date],",",[End Time]) [Duration] =[Ending]-[Starting]