Determine "on time" status

[Teri]

I'm trying to write a formula to determine if a delivery is on time. In the
example below, I used =IF(O6<=N6,"Yes","No") and Excel returned "No", but
this shipment was on time. What am I doing wrong?

Column N Column O
Scheduled Delivery Date / Time Actual Delivery Date / Time
25-May-10 05:00 PM 25-May-10 09:00 AM

 

[Dave Peterson]

I would try a couple of things.

First, give N6 and O6 an unambiguous date/time format.
Like:
mmmm dd, yyyy hh:mm:ss

If the values don't change, then your entries aren't really date and times.
They're just plain old text. You'll want to convert them to real dates/times.

If they do change to that nice format, then check the years. Maybe one of those
10's is 1910 and the other is 2010????

 

[Tom-S]

Assuming you have the date-times in N6 and O6, and that these cells are
formatted as dd-mmm-yy hh:mm AM/PM, then your formula should work. I just
tried it out and it seemed ok to me.

Post again if your formats are ok.

Regards,

Tom

 

[Teri]

Not sure what you mean by AM/PM; is there another way to format other than
how they are written in my example?

 

[Tom-S]

If you highlight both cells N6 and O6, then click Format > Cells > Number
tab, and select Custom in the Category box, then underneath Type delete
whatever is in the the box and type in dd-mmm-yy hh:mm AM/PM

What that will do is format a 24 hour time you enter into a 12 hour time
followed by either AM or PM as appropriate.

So for the date-times you gave as examples, they would be entered as follows:
25-5-10 17:00 and 25-5-10 09:00
but they will appear as 25-May-10 05:00 PM and 25-May-10 09:00 AM

There are lots of other ways to format dates and times. While you're on the
Number tab of Format > Cells, have a look at both the Date and Time
categories and scroll through the examples in the 2nd box below Type.

Post again if you need more.

Regards,

Tom

 

[Teri]

Thanks guys; I really appreciate your help. Neither of those suggestions
worked. The funny thing is, I have a row above and a row below the one in
question and they work fine. The difference is they were delivered in the PM
instead of the AM.

Scheduled Dely Date / Time Actual Dely Date / Time On / Time (Yes / No)
18-May-10 04:00 PM 18-May-10 03:36 PM Yes
25-May-10 05:00 PM 25-May-10 09:00 AM No
25-May-10 05:00 PM 24-May-10 02:40 PM Yes

 

[Tom-S]

Pity. Can you tell us what format you have got applied to the columns N and O
where the date-times are stored?

Could you also on your sheet have 2 formulas (both in cells formatted as
number):
=N6 and =O6
and tell us what answer you get from these formulas.

Regards,

Tom

 

[Teri]

Tom,
I formatted exactly as you described. Not sure I understand what you mean
by the =N6 and =O6 portion of your answer.

 

[Tom-S]

What I mean is pick 2 cells that you're not currently using somewhere on your
sheet. In the first cell type =N6 and in the other cell type =O6. At first
these cells will probably show the dates that you've entered in N6 and O6,
but change the formatting of these 2 cells to number and then you should see
40324.71 in the first cell and 40323.38 in the second. These are the Excel
serial number equivalents of your date-times (if the date-times are formatted
correctly).

By the way, after you formatted the cells as suggested did you re-enter the
date-times in N6 and O6?

Regards,

Tom

 

[Teri]

OMG!!!! That's it!!! When I did what you suggested and entered =N6 and =O6
I got 40232.71 and 25-May-10 09:00 AM but that's because I DID re-enter
column O but didn't re-enter column N. Works perfect!!!

Thanks for your patience and for sharing your knowledge!!