Calculate time
Hi Everyone,
years ago, I was told that smartsheet does not calculate time.
Has the ability to calculate time been added?
It would be nice to move this task over to smartsheet from Excel.
I am looking to get the results in the blue column.
Best Answer

Hi Jeff,
Thank you SO!! much. This is beyond the simple answer i expected.
It is odd that Smartsheet cannot add this. Seems like it would be used by many off site worker to log in hours of work.
I got puled away for work and will give it a shot this weekned in a quite space.
Have a great weekend.
Joseph.
Answers

No, time calculations have not been added to Smartsheet. It is still a giant pain to do what you want. However, I did just implement something similar this week involving hours, net days, etc.
First I would recommend going to 24hour time. 1pm = 13:00, etc., without AM / PM. Then, get ready for a ton of nested IFs, helper columns, and converting text to values in order to do the math, and then putting them back together as strings.
To calculate your Leave Warehouse time:
Lets call the first column DockTime, the second column TravelTime. Add a helper column called DockHour. For DockHour:
=IF(LEFT(DockTime@row, 2) <> ":", VALUE(LEFT(DockTime@row, 2)), VALUE(LEFT(DockTime@row, 1)))
Add a helper column called DockMinute. For DockMinute:
=IF(RIGHT(DockTime@row, 2) = "00", 60, VALUE(RIGHT(DockTime@row, 2)))
TravelHour helper column:
=IF(LEFT(TravelTime@row, 2) <> ".", VALUE(LEFT(TravelTime@row, 2)), VALUE(LEFT(TravelTime@row, 1)))
TravelMinute helper column:
=IF(RIGHT(TravelTime@row, 2) = "00", 60, VALUE(RIGHT(TravelTime@row, 2)))
For the Leave warehouse time column:
=IF(OR(TravelMinute@row > 0, TravelMinute@row < DockMinute@row), (DockHour@row  TravelHour@row), (DockHour@row + 1  TravelHour@row)) + ":" + IF(AND(DockMinute@row = 60, TravelMinute = 60, "00", IF(DockMinute@row > TravelMinute@row, (DockMinute@row  TravelMinute@row), (DockMinute@row + TravelMinute@row))
Now I'm not making any guarantees, but if you switch to 24hour time with no AM/PM, and travel time as H.QTR HR, you should get the correct 24hour time HH:MM to leave the warehouse.
Do the same thing with the Hours Worked:
StartHour helper column:
=IF(LEFT(StartWork@row, 2) <> ":", VALUE(LEFT(StartWork@row, 2)), VALUE(LEFT(StartWork@row, 1)))
StartMinute helper column:
=IF(RIGHT(StartWork@row, 2) = "00", 60, VALUE(RIGHT(StartWork@row, 2)))
EndHour helper column:
=IF(LEFT(EndWork@row, 2) <> ":", VALUE(LEFT(EndWork@row, 2)), VALUE(LEFT(EndWork@row, 1)))
EndMinute helper column:
=IF(RIGHT(EndWork@row, 2) = "00", 60, VALUE(RIGHT(EndWork@row, 2)))
Total Hours:
=IF(AND(StartHour@row < EndHour@row, OR(EndMinute@row > 0, EndMinute@row < StartMinute@row)), (StartHour@row  EndHour@row), IF(AND(StartHour@row > EndHour@row, OR(EndMinute@row > 0, EndMinute@row < StartMinute@row)), (StartHour@row + 1  EndHour@row)) + ":" + IF(AND(StartMinute@row = 60, EndMinute@row = 60, "00", IF(StartMinute@row > EndMinute@row, (StartMinute@row  EndMinute@row), (StartMinute@row + EndMinute@row))
HAVE FUN!! 😀
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

I quickly tested this out this morning.
First, TravelTime needs to be stored as text. So to get 1.30, you'd need to enter it as '1.30
DockHour:
=IF(MID(DockTime@row, 2, 1) <> ":", VALUE(LEFT(DockTime@row, 2)), VALUE(LEFT(DockTime@row, 1)))
TravelHour:
=IF(MID(TravelTime@row, 2, 1) <> ".", VALUE(LEFT(TravelTime@row, 2)), VALUE(LEFT(TravelTime@row, 1)))
In the Warehouse leave time formula, I had a few errors (a misplaced parentheses and a missing @row. Fixed:
=IF(OR(TravelMinute@row > 0, TravelMinute@row < DockMinute@row), (DockHour@row  TravelHour@row), (DockHour@row + 1  TravelHour@row)) + ":" + IF(AND(DockMinute@row = 60, TravelMinute@row = 60), "00", IF(DockMinute@row > TravelMinute@row, (DockMinute@row  TravelMinute@row), (DockMinute@row + TravelMinute@row)))
StartHour:
=IF(MID(StartWork@row, 2, 1) <> ":", VALUE(LEFT(StartWork@row, 2)), VALUE(LEFT(StartWork@row, 1)))
EndHour:
=IF(MID(EndWork@row, 2, 1) <> ":", VALUE(LEFT(EndWork@row, 2)), VALUE(LEFT(EndWork@row, 1)))
The TOTAL HOURS formula needs work. I'll get back to you.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hi Jeff,
Thank you SO!! much. This is beyond the simple answer i expected.
It is odd that Smartsheet cannot add this. Seems like it would be used by many off site worker to log in hours of work.
I got puled away for work and will give it a shot this weekned in a quite space.
Have a great weekend.
Joseph.

Did Jeff ever post his corrections to the total hours formula? I am looking to use this for my sheet.
Help Article Resources
Categories
Check out the Formula Handbook template!