Prev: SUM IF: How do I sum the data in a column based on 2 other columns
Next: Invisible Black Box is Printing
From: Norm on 19 May 2010 11:59
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!!!
From: Ron Rosenfeld on 19 May 2010 12:14
On Wed, 19 May 2010 08:59:01 -0700, Norm <Norm(a)discussions.microsoft.com>
wrote:

>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
From: T. Valko on 19 May 2010 12:22
The general formula for time difference when the time might span past
midnight is:

=MOD(End-Start,1)

Or

=End-Start+(End<Start)

--
Biff
Microsoft Excel MVP


"Norm" <Norm(a)discussions.microsoft.com> wrote in message
news:80EE3E25-3FDA-4693-B479-A51119EA19D0(a)microsoft.com...
>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!!!


From: Norm on 19 May 2010 12:59
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" wrote:

> On Wed, 19 May 2010 08:59:01 -0700, Norm <Norm(a)discussions.microsoft.com>
> wrote:
>
> >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
> .
>
From: Ron Rosenfeld on 19 May 2010 14:48
On Wed, 19 May 2010 09:59:01 -0700, Norm <Norm(a)discussions.microsoft.com>
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
 |  Next  |  Last
Pages: 1 2
Prev: SUM IF: How do I sum the data in a column based on 2 other columns
Next: Invisible Black Box is Printing