Calculating a time before midnight and after midnight
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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1)) + VALUE(RIGHT([Edispatch 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 "Edispatch time" is before midnight and "Actual time of arrival" is after midnight?
Many thanks in advance for your kind assistance.
Best regards,
Andi
Answers

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.

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

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.

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"  "Edispatch 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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1)) + VALUE(RIGHT([Edispatch 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.

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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1)) + VALUE(RIGHT([Edispatch time]@row, 2)) / 60)

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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1)) + VALUE(RIGHT([Edispatch time]@row, 2)) / 60))
Would you be able to give me a hint?


No, it should stay open, no limit.


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


The date is not relevant in my formulas.
I have a separate row "Date of call out" (not formulated).

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?

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 ("Edispatch 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?

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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1) + "." + RIGHT([Edispatch 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([Edispatch time]@row, FIND(":", [Edispatch time]@row)  1)) + VALUE(RIGHT([Edispatch time]@row, 2)) / 60))
Help Article Resources
Categories
Check out the Formula Handbook template!