Calculating a time before midnight and after midnight

Options

Dear Smartsheet,

I hope this message reaches you all safe and healthy.

I am trying to upgrade an existing page which is already partially formulated.

I reached a problem where we have received a call before midnight and the arrival time reached after midnight.

See below error appeared:


Under column "Sum" I have the following formula:

=((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60))

Under "SLA Standard" I have the following formula:

=INT(Sum@row) + ":" + IF((Sum@row - INT(Sum@row)) * 60 < 10, "0") + (Sum@row - INT(Sum@row)) * 60


How would I be able to get the correct calculation, in terms of time (under "SLA Standard" column), if the "E-dispatch time" is before midnight and "Actual time of arrival" is after midnight?


Many thanks in advance for your kind assistance.

Best regards,

Andi

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    HERE is a link to a thread that has multiple time solutions. There should be one in there that has this built in.


    The basic idea:

    Start Time = Start Time

    End Time = End Time + (24 * # of Days Difference)


    So basically when you convert the end time into a numerical value, you then take 24, multiply it by the number of days difference, then add that to the end time number. Now your end time minus start time should work.


    Example:

    Start: 1 Jan 20 @ 2300

    End: 2 Jan 20 @ 0100


    Your Start Time Number would be 23


    And your end time number would be:

    Time = 1

    Date Difference = 1

    1 day = 24 hours

    24 + 1 = 25


    So now we have Start = 23 and End = 25. Now when we take End minus Start (25 - 23) we get the correct result of 2.

  • Andi Reisner
    Options

    Hiya Paul,

    Many thanks for your reply. Weird usually I get notifications to my mailbox of your replies. This time not. Never mind.

    Anyway, point is taken. However, where would I have to insert the additional "day"+1 without messing the reset rowed formulas? Would you suggest to add another row for it or simply adding it to the existing formulas?

    Many thanks again.

    Best regards,

    Andi

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I would need to see all of the formulas in your sheet to be able to see how they work together and where exactly it would go.


    What you want to look for is where you are subtracting one from the other whether it be a standalone formula in a separate column or tucked into a larger formula.

    The "+ day" solution would be added onto the end time BEFORE the subtraction takes place.

  • Andi Reisner
    Options

    Hi Paul,

    See again screenshot below of the page:

    Under "Sum" row I have the formula that subtracts (in terms of time) "Actual time of arrival" - "E-dispatch time" (for your note, this row i keep hidden):

    • =((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60)

    Under "SLA Standard" row I have the total summary, in terms of time:

    =INT(Sum@row) + ":" + IF((Sum@row - INT(Sum@row)) * 60 < 10, "0") + (Sum@row - INT(Sum@row)) * 60


    That says, line 9 should be corrected so that "+1 day" will be effective on the formula.

    I am open for any changes you may think will work better.


    Many thanks again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The [Actual Time of arrival] portion at the start of your Sum column is going to be the end time that you need to ad the day to.

    =((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) *** + day solution goes here *** - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60)

  • Andi Reisner
    Options

    Hiya Paul,

    Sorry, can you be more accurate.

    I was trying to insert the formula below but got some error.

    =((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) + 1 DAY - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60))


    Would you be able to give me a hint?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Andi Reisner
    Options

    No, it should stay open, no limit.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Andi Reisner
    Options

    My end time is the "Actual time of arrival" row.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Andi Reisner
    Options

    The date is not relevant in my formulas.

    I have a separate row "Date of call out" (not formulated).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you are overlapping dates then you will need to have some kind of logic to establish an end date to go along with your end time. Will it ever be past the next day? Is it possible to start on one day and then end two or three (or more) days later, or would it be assumed that if there is overlap into another date then it is next day?

  • Andi Reisner
    Options

    Thanks Paul,

    No, we are talking only about few hours gap, where sometimes the start hour falls shortly before midnight and the arrival time ends maximum couple of hours after midnight. Therefore, a plus one day will be enough. But I am not exactly sure how and where to place this in my existing formulas.

    Have a look at the example below, in the first line the call out time was ("E-dispatch time") is 5:45 and the arrival time was 6:35. Hence, 0:50hrs (formulated in "SLA Standard" row) later

    In the line below the call out time was before midnight (23:34) and the arrival time was at 02:39. Here the result went ballistic. Under "SLA Standard" should obviously be 3:05.

    Any solution how we can correct this error?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If it will never be more than a single date's difference, then we should be able to use this...

    =((VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1)) + VALUE(RIGHT([Actual time of arrival]@row, 2)) / 60) + IF(VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1) + "." + RIGHT([E-dispatch time]@row, 2))> VALUE(LEFT([Actual time of arrival]@row, FIND(":", [Actual time of arrival]@row) - 1) + "." + RIGHT([Actual time of arrival]@row, 2)), 24, 0) - (VALUE(LEFT([E-dispatch time]@row, FIND(":", [E-dispatch time]@row) - 1)) + VALUE(RIGHT([E-dispatch time]@row, 2)) / 60))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!