Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Hours tracking in schedule.

✭✭✭✭
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!


image.png

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:

image.png


End Time Column Formula:

image.png


Answers

  • Employee

    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.

    image.png

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions