Determine "on time" status
 

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

Determine "on time" status
 

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????

Teri wrote:

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

Determine "on time" status
 

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

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

Determine "on time" status
 

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

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

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

Determine "on time" status
 

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

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

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

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

Determine "on time" status
 

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


"Dave Peterson" wrote:

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????

Teri wrote:

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
.

Determine "on time" status
 

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

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


"Dave Peterson" wrote:

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????

Teri wrote:

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
.

Determine "on time" status
 

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

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

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


"Dave Peterson" wrote:

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????

Teri wrote:

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
.

Determine "on time" status
 

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

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

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

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


"Dave Peterson" wrote:

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????

Teri wrote:

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
.

Determine "on time" status
 

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!!

"Tom-S" wrote:

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

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

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

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


"Dave Peterson" wrote:

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????

Teri wrote:

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
.