Determine "on time" status
in [Functions]
|
From: Teri on 3 Jun 2010 14:44 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
From: Dave Peterson on 3 Jun 2010 16:04 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 16:15 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 >
From: Teri on 3 Jun 2010 16:26 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 > >
From: Tom-S on 3 Jun 2010 16:43
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 > > > |