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

[Matheus_Trilico]

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]

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]

@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]

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

[James_W_Rezin]

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

[James_W_Rezin]

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]

@Aleksi_Alkio

Amazing! It works!!! Thank you again!

[Matheus_Trilico]

@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]

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

[James_W_Rezin]

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