Time difference across midnight

[Norm]

I have read all the other threads on this subject and they work fine for me
untill I need to cross midnight and the value is the time subtracted from 24
here are the cells:
Date Store Actual Arrival Time Est Arrival Time Variance
4/6/2010 33369 11:26:00 PM 12:27:00 AM -22:59
Can someone let me know what I have missed?
Cheers!!!

 

[Ron Rosenfeld]

I have read all the other threads on this subject and they work fine for me
untill I need to cross midnight and the value is the time subtracted from 24
here are the cells:
Date Store Actual Arrival Time Est Arrival Time Variance
4/6/2010 33369 11:26:00 PM 12:27:00 AM -22:59
Can someone let me know what I have missed?
Cheers!!!

You probably want something like:

=Est-Actual+(Est<Actual)

--ron

 

[T. Valko]

The general formula for time difference when the time might span past
midnight is:

=MOD(End-Start,1)

Or

=End-Start+(End<Start)

 

[Norm]

Thanks Ron,
That helped but I have another problem when it is a negetive it goes back to
23:59

Date Store Actual Arrival Time Est Arrival Time Variance
4/15/2010 33035 8:00:00 PM 8:00:00 PM 0:00
4/15/2010 34287 8:46:00 PM 8:45:00 PM 23:59
4/15/2010 39210 9:28:00 PM 9:36:00 PM 0:08
Cheers!!

 

[Ron Rosenfeld]

Thanks Ron,
That helped but I have another problem when it is a negetive it goes back to
23:59

Date Store Actual Arrival Time Est Arrival Time Variance
4/15/2010 33035 8:00:00 PM 8:00:00 PM 0:00
4/15/2010 34287 8:46:00 PM 8:45:00 PM 23:59
4/15/2010 39210 9:28:00 PM 9:36:00 PM 0:08
Cheers!!

If there are going to be "negative times" that you want displayed in Excel time
format, you will have to use the 1904 date system.

But before we get into that, we need to discuss how you would represent
negative variances.

In your first post, you gave an example of but wanted to know how to handle the
"crossing midnight" issue.

Date Store Actual Arrival Time Est Arrival Time Variance
4/6/2010 33369 11:26:00 PM 12:27:00 AM

I assumed that the "Est Arrival Time" was after midnight.

If it is NOT always the case that an Est Arrival Time being "earlier" than an
Actual Arrival time means that the Est Arrival Time occurred on the following
day (i.e. after midnight), then you will not be able to use the method
proposed.

You will have to enter Date as well as time for both "Actual" and "Est". Then
you can subtract one from the other directly.

Another approach would be to use a flag column to indicate if Est is on the
Same day or the Next day compared with Actual, and incorporate that into the
formula.

So consider what I have written, and post back with how you want to handle this
issue, and we can devise a method.

--ron

 

[Norm]

Thanks Ron
This worksheet will reflect what time the delivery is expected and what time
the delivery showed up ie. Est. arrival time vs. Actual arrival time I need
to show the varience whether the delivery is early or late by the number of
minutes (or hours)
I would like to see the varience in positive and negetive values
Does this make sense?
What would be the more usful formula?
Cheers!!!

 

[Ron Rosenfeld]

Thanks Ron
This worksheet will reflect what time the delivery is expected and what time
the delivery showed up ie. Est. arrival time vs. Actual arrival time I need
to show the varience whether the delivery is early or late by the number of
minutes (or hours)
I would like to see the varience in positive and negetive values
Does this make sense?
What would be the more usful formula?
Cheers!!!

My suggestion would be to include the date as well as the time in your Est. and
Actual columns.

Then, with a formula of

=Est - Actual

you will get a negative value if the delivery is late (i.e. Actual is after
Est).

If you format the cell as [h]:mm, you will get a result of hours:seconds.
However, you will need to select the 1904 date system in order that the
negative values display, and you will need to do this before entering the
dates, otherwise they will be off by four years and a day.

You could display the result, with the 1900 date system, as a text string, but
this would make subsequent math operations much more complex.



--ron