A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Determine "on time" status



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 07:44 PM posted to microsoft.public.excel.worksheet.functions
Teri
external usenet poster
 
Posts: 155
Default 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

Ads
  #2  
Old June 3rd, 2010, 09:04 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #3  
Old June 3rd, 2010, 09:15 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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

  #4  
Old June 3rd, 2010, 09:26 PM posted to microsoft.public.excel.worksheet.functions
Teri
external usenet poster
 
Posts: 155
Default 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

  #5  
Old June 3rd, 2010, 09:43 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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

  #6  
Old June 3rd, 2010, 10:07 PM posted to microsoft.public.excel.worksheet.functions
Teri
external usenet poster
 
Posts: 155
Default 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
.

  #7  
Old June 3rd, 2010, 10:16 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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
.

  #8  
Old June 3rd, 2010, 10:37 PM posted to microsoft.public.excel.worksheet.functions
Teri
external usenet poster
 
Posts: 155
Default 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
.

  #9  
Old June 3rd, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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
.

  #10  
Old June 3rd, 2010, 11:18 PM posted to microsoft.public.excel.worksheet.functions
Teri
external usenet poster
 
Posts: 155
Default 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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.