Formulas for Calculating Time



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L_123 Yours is definitely shorter, but I have no clue which one would be more of a draw on the back-end.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 10/31/22

    [H]:mm - [H]:mm = [H]:mm

    Columns are:

    Ending Time "Out"

    Starting Time "In"

    =VALUE(LEFT([email protected], FIND(":", [email protected]) - 1)) - IF(VALUE(RIGHT([email protected], 2)) > VALUE(RIGHT([email protected], 2)), 0, 1) - VALUE(LEFT([email protected], FIND(":", [email protected]) - 1)) + ":" + IF(LEN((IF(VALUE(RIGHT([email protected], 2)) > VALUE(RIGHT([email protected], 2)), 0, 1) + VALUE(RIGHT([email protected], 2)) - VALUE(RIGHT([email protected], 2)))) = 1, 0, "") + (IF(VALUE(RIGHT([email protected], 2)) > VALUE(RIGHT([email protected], 2)), 0, 1) + VALUE(RIGHT([email protected], 2)) - VALUE(RIGHT([email protected], 2)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L_123 I feel like a basic subtraction of start time from end time is listed in here. I'm going to have to dig through when I have some extra time and consolidate all of the solutions onto a single page again.

  • Katie Lafferty
    edited 12/12/22

    Hi Paul, have you had any time to think about my snow plow formulas? We had our first snowfall last night :) Remember I used your formulas to calculate total hours that may span over multiple days (military time). I need to now calculate these additional things:

    1. Straight Time - any hours during 7am-3pm that may span over multiple days (1-3 consecutive days possible).
    2. Overtime Hours - any hours not during 7am-3pm that may span over multiple days (1.5 hourly rate) & the same formula to calculate work on a holidays (2.0 double hourly rate for these hours).
    3. FEMA Time - which is essentially the same as Overtime Hours but will include the vehicles they drive. Once formulas for Straight Time & Overtime are figured out, I can apply to the FEMA stuff.

    It's the spanning over multiple days that has me stumped. The in/out of the 7am-3pm zone during one event has my head spinning.


  • SteCoxy
    SteCoxy ✭✭✭✭✭✭

    so frustrating that duration/time is still not a thing within Smartsheet without all of these technically amazing workarounds. I wonder if and when Smartsheet will ever release this as a proper functionality? I was advised 5 years ago that they were actively working on it, but nothing seems to be on the cards any time soon :(

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @SteCoxy I am fairly certain that it is in fact being worked on when they have the available Devs. There are a lot of complexities and variables though to program in once you start accounting for time zones and user preferences and whatnot plus all of the different ways that it could potentially be used alongside variable formats to account for. It is definitely not an easy job to tackle.

  • I've been reading through this thread (and several others) and am very close to what I'm looking for, but can't seem to find what I need to complete my calculations.

    I have a deployment plan schedule that has start time, estimated duration and end time creating a running schedule where the end time is the start time for the next task. These tasks generally run continuously over the course of 2-3 days. I have changed to military time to use formulas I found for similar use cases and can get the time to show correctly until the end time goes to the next day. The times keep adding together so it becomes 24:35 instead of 00:35.

    Attached is the excel example I'm trying to replicate in Smartsheet and screenshots of how far I've gotten and the formula's I'm using.