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!
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.
@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โฆ
Try withโฆ ([END DATE]+([END TIME] - โ00:00:00โ)) - ([START DATE]+([START TIME] - โ00:00:00โ))
@Matheus_Trilico [Starting] =Concatenate([Start Date],",",[Start Time]) [Ending] =Concatenate([End Date],",",[End Time]) [Duration] =[Ending]-[Starting]
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.
Amazing! It works!!! Thank you again!
@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โฆ
Try withโฆ ([END DATE]+([END TIME] - โ00:00:00โ)) - ([START DATE]+([START TIME] - โ00:00:00โ))
@Matheus_Trilico [Starting] =Concatenate([Start Date],",",[Start Time]) [Ending] =Concatenate([End Date],",",[End Time]) [Duration] =[Ending]-[Starting]
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |