Hours tracking in schedule.

Abdul Tahlil

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:

HeatherD.

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

Abdul Tahlil

Thank you @HeatherD. This helped get me to the finish line!