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:

Can someone please post the formula for the calculation between column start time and column end time  with the format 7:30pm

@kawind That solution is already posted in a number of places here in this thread.

Hello Darkness, my old friend!
As a followup to my previous comment, I have found a gap in my ducktape solution.
My formula defined a case as out of SLA for the scenario of the weekend, see:
Full view:
Networkdays formula correctly provided result of 2, considering 1/19  Friday, and 1/22  Monday.
Correct result would be here:
2416.33 = ~7.7 from Friday
~12.8 Monday
Total: 20.5 hours = <24 = IN SLA

NETWORKDAYS Formula:
=IFERROR(NETWORKDAYS([Start Date]@row, [End Date]@row), "")
SLA_Time Formula:
=IFERROR(IF([SUM_Days]@row > NETWORKDAYS@row, [SUM_Days]@row  ([SUM_Days]@row  NETWORKDAYS@row), [SUM_Days]@row), "")

Do you have any idea how to fix it up?
Thanks,
Romano
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives