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 Smart-sheet 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 24-hour 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 24-hour time with no AM/PM, and travel time as H.QTR HR, you should get the correct 24-hour 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 Smart-sheet 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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 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
Check out the Formula Handbook template!