# 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.

(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.

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