Difference in 2 dates

  • Thread starter Thread starter Felipe
  • Start date Start date
F

Felipe

Gerry,

I assume you want to calculate the number of days between
both dates.

If A1=Order date and A2 =ship date.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))-DATE(LEFT
(A1,4),MID(A1,5,2),RIGHT(A1,2))

Format cell as number

Regards,
Felipe
 
or , if they are dates, more simply

=A2-A1

and format as number, or general

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Felipe

I received an error message when I entered the formula:

E2=Order date and F2 =ship date.

=DATE(LEFT(F2,4),MID(F2,5,2),RIGHT(F2,2))-DATE(LEFT
(E2,4),MID(E2,5,2),RIGHT(E2,2))
 
Gerry,

Are yor data entered as dates or numbers?
If they are dates you can simply substact them as Bob said.

I had assumed your data were numbers. I just tested the
formula and it works, I have:
A12=20031015
A13=20031130
and the formula is: =+DATE(LEFT(A13,4),MID(A13,5,2),RIGHT
(A13,2))-DATE(LEFT(A12,4),MID(A12,5,2),RIGHT(A12,2))

Possible causes for the error:
I assumed dates are written as year/month/day, is it so?
Does all your data have 8 caracters?
If shipdate is smaller than Orderdate you should have an
error message, but I don't think that combination is
possible.

Let me know if you could solve the problem.

Regards,
Felipe
 
Felipe :

The formula is working, but the # of days are wrong

My data is:

E2 20031010
F2 20030428

formula is:=+DATE(LEFT(F2,4),MID(F23,5,2),RIGHT
(F2,2))-DATE(LEFT(E2,4),MID(E12,5,2),RIGHT(E2,2))

Result is 165

Where is my mistake?

Thank you very much for your assistance.

Gerry
 
Gerry,

I also get 165 as a result, and I think it's correct, I
have even counted the days between march 28 and october 10!
(if you want to include BOTH days in the calculation add 1
to the formula, i.e. from march 28 at 0:00 to october 28
at midnight)

However, if I use your data, the result I get is -165, I
am confused, how can the shiping date be prior to the
order date?

A very good formula for comparing dates is DATEDIF. It's
not included in the Excel help but Chip Pearson has a very
complete explanation for it at:
http://www.cpearson.com/excel/datedif.htm
Just be sure to insert dates in it.

Regards,
Felipe
 
Back
Top