Calculating a time before midnight and after midnight

Andi Reisner

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

Paul Newcome

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

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

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

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

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

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

Do you have a start and end date?

Andi Reisner

No, it should stay open, no limit.

Paul Newcome

Then how do you determine your end time?

Andi Reisner

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

Paul Newcome

Ok. But you do not have an end date?

Andi Reisner

The date is not relevant in my formulas.

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

Paul Newcome

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

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

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))