Hours tracking in schedule.
I need some help troubleshooting a formula. I have viewed the numerous posts on time tracking in smartsheet and have used the formulas in this post: https://community.smartsheet.com/discussion/67111/calculating-military-time-from-am-pm-format.
My problem is the when any time past midnight is still showing up as "PM" instead of switching to "AM".
Any help would be greatly appreciated. Thanks!
In the above picture, you can see at the last row, the 5hr duration should make the end time 1:30am instead of pm.
End Column Formula:
End Time Column Formula:
Answers
-
Hi @Abdul Tahlil !
I think I've come up with some solutions for you. With regard to the formula in the End column, you can basically set it up like this: =IF(formula you want to run >= 24, formula you want to run - 24, formula you want to run). This will subtract 24 from the result if it's over 24, and bring it back down to the actual amount.
The formula I ended up with for the End column looked like this:
=IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) + IF(CONTAINS("p", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) <> 12, 12), IF(CONTAINS("a", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) = 12, -12))) + (VALUE(MID([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) + 1, 2)) / 60) + Dur@row >= 24,
VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) + IF(CONTAINS("p", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) <> 12, 12), IF(CONTAINS("a", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) = 12, -12))) + (VALUE(MID([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) + 1, 2)) / 60) + Dur@row - 24,
VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) + IF(CONTAINS("p", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) <> 12, 12), IF(CONTAINS("a", [Target Time (Assumed)]@row), IF(VALUE(LEFT([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) - 1)) = 12, -12))) + (VALUE(MID([Target Time (Assumed)]@row, FIND(":", [Target Time (Assumed)]@row) + 1, 2)) / 60) + Dur@row)
However, I then ran into a problem if the End Time was supposed to be in the 12am hour; the result in the End Time column showed as 0:30am. So, for the End Time formula, I added an IF to the hour portion of the formula so that, if the End value is less than 1, it should show as 12:
=IF(End@row < 1, 12, MOD(INT(End@row), 12)) + ":" + IF((End@row - INT(End@row)) * 60 < 10, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "pm", "am")
I hope this helps!
Best,
Heather
-
Thank you @HeatherD. This helped get me to the finish line!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!