On Wed, 19 May 2010 09:59:01 -0700, Norm
wrote:

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