Hours tracking in schedule.

Options
Abdul Tahlil
Abdul Tahlil ✭✭✭✭
edited 04/27/23 in Formulas and Functions

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

  • HeatherD.
    HeatherD. Moderator
    Options

    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
    Abdul Tahlil ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!