Formulas for Calculating Time
Comments

I am trying to calculate number of minutes between start and end time. It is working fine untill there is no Date change. On Date change it gives me negative minutes since i am using 24 hour clock.
=(VALUE(LEFT([Actual End (UTC+0300)]@row, 2)) + (VALUE(RIGHT([Actual End (UTC+0300)]@row, 2)) / 60  (VALUE(LEFT([Actual Start (UTC+0300)]@row, 2)) + (VALUE(RIGHT([Actual Start (UTC+0300)]@row, 2)) / 60)))) * 60

I am getting an invalid formula when trying to calculate the total time between a start time and stop time. Can anyone help with the correct formula?

You can use the new time function to achieve the time difference you are looking for, see the community post here.
Also a community member put together this awesome video on the time function, worth the watch.

@Windrich Group Your syntax is off.
Try something like this:
=TIME(TIME([Stop Time]@row)  TIME([Start Time]@row)) * 24
The above will output the duration in hours.

@Paul Newcome with the save again! Thanks Paul. You should do a Smartsheet webinar on the time function 😄

Hey Smart Heads!
Excellent job @Paul Newcome on the date formula master article!
I have tried to do similar exercise to Calculating Time Worked for Employees :
Scenario:
 IR Time and Hand Over Time is provided in strange format, hence I have used following:
=LEFT([IR Time]@row, 5)
=LEFT([Hand Over Time]@row, 5)
Formula in LeadTime Calculation:
=((VALUE(LEFT([Hand Over Time  Hours]@row, FIND(":", [Hand Over Time  Hours]@row  1)) + VALUE(RIGHT([Hand Over Time  Hours]@row, 2)) / 60) + ([Hand Over Date]@row  [IR Date]@row) * 24)  (VALUE(LEFT([IR Time  Hours]@row, FIND(":", [IR Time  Hours]@row)  1)) + VALUE(RIGHT([IR Time  Hours]@row, 2)) / 60))
is giving me #INVALID OPERATION error : (
 I suspect that the issue might be triggered by using =LEFT formula to extract 5 digit
 well... I have just came up with an idea to slightly modify the formula to extract the time from IR  Time and not from IR Time  Hours (with LEFT), will keep you updated.
@Upatde
I have additionally tried following:
=((VALUE(LEFT([Hand Over Time]@row, FIND(":", [Hand Over Time]@row  1)) + VALUE(MID([Hand Over Time]@row, 4, 2)) / 60) + ([Hand Over Date]@row  [IR Date]@row) * 24)  (VALUE(LEFT([IR Time]@row, FIND(":", [IR Time]@row)  1)) + VALUE(MID([IR Time]@row, 4, 2)) / 60))
with the same results:
If you can help, please let me know, thanks!
Romano

@Romano el Polako I haven't looked through in too much detail yet, but one thing I do notice is that you have multiple parenthesis either missing or misplaced. Which formula from the thread are you trying to replicate?

Hey @Paul Newcome
Thank you for quick response, I was trying to use your formula:
=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row)  1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row  [Start Date]@row) * 24)  (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row)  1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
Thanks,
Romano!

@Romano el Polako In that case, make sure you only change column names. Adjusting the parenthesis will break the formula.

Hey @Paul Newcome
I don't know how, why and by what special force, but the formula actually works after aligning nomenclature with the column names from your query, outstanding, thanks again!
Romano

Hey @Paul Newcome and Smart Heads!
Sorry for another request, but I would like to stepup the game when it comes to time calculation.
 As presented above, I have access to A. Start Date + Time and B. End Date + Time.
 Thanks to Palu's Formula, I have:
 Difference in Hours + Minutes
 SUM: in 1/100 values
 Sum_Days: =SUM@row / 24
 As a next step, I have added a column for NETWORKDAYS =NETWORKDAYS([Start Date]@row, [End Date]@row)
 And finally added a column to calculate SLA Time (SLAs (service level agreements), time from intake to task completed):
 =IF([SUM_Days]@row > NETWORKDAYS@row, [SUM_Days]@row  ([SUM_Days]@row  NETWORKDAYS@row), [SUM_Days]@row)
 In other words, IF SUM Days is longer that count of networkdays, then we consider networkdays as SLA. If SUM Days is shorter than Networkdays, then we consider SUM Days.
Desired next Steps:
 Define SLA in more precise way. Substract from SUM_Days (in hours) Time of Weekends and Holidays (in hours as well).
 I think that this form can be pretty complex, but having this formula can be helpful for many SmartUsers.
 Thinking long term, we could raise a request to have =NETWORKHOURS formula in SmartSheet to avoid any walkarounds.
 Looking forward to hearing from you, Thanks! Romano

@Update:
 Short Term Goal: I think I have managed to endup with pretty solid count of net days, by adjusting Project Settings, see: