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 15:57
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" wrote:

> 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
> .
>
From: Ron Rosenfeld on 19 May 2010 21:46
On Wed, 19 May 2010 12:57:01 -0700, Norm <Norm(a)discussions.microsoft.com>
wrote:

>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
First  |  Prev  | 
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