Determine "on time" status
in [Functions]
|
From: Teri on 3 Jun 2010 17:07 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 > . >
From: Tom-S on 3 Jun 2010 17:16 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 > > . > >
From: Teri on 3 Jun 2010 17:37 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 > > > . > > >
From: Tom-S on 3 Jun 2010 18:02 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 > > > > . > > > >
From: Teri on 3 Jun 2010 18:18
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 > > > > > . > > > > > |