Calculate time

Options

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

  • Joseph Farahat
    Joseph Farahat ✭✭✭
    Answer ✓
    Options

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Joseph Farahat

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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!

  • Joseph Farahat
    Joseph Farahat ✭✭✭
    Answer ✓
    Options

    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.

  • tim_sims23
    Options

    Did Jeff ever post his corrections to the total hours formula? I am looking to use this for my sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!