Difference in 2 dates

Difference in 2 dates

Post by Gerr » Sat, 18 Oct 2003 04:42:36

A little help please:

I have an order date 20030110 and a ship date 20030405.
What is the formula for calculating?

Thanks for your help.It is very much appreciated.


Difference in 2 dates

Post by Felip » Sat, 18 Oct 2003 05:37:26


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

If A1=Order date and A2 =ship date.

Format cell as number



Difference in 2 dates

Post by Bob Philli » Sat, 18 Oct 2003 06:06:18

or , if they are dates, more simply


and format as number, or general


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

Difference in 2 dates

Post by gerr » Sat, 18 Oct 2003 06:25:21


I received an error message when I entered the formula:

E2=Order date and F2 =ship date.



Difference in 2 dates

Post by Felip » Sat, 18 Oct 2003 06:50:45


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:
and the formula is: =+DATE(LEFT(A13,4),MID(A13,5,2),RIGHT

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

Let me know if you could solve the problem.


Difference in 2 dates

Post by Gerr » Sat, 18 Oct 2003 07:47:47

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

Result is 165

Where is my mistake?

Thank you very much for your assistance.



Difference in 2 dates

Post by Felip » Sat, 18 Oct 2003 09:09:38


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:
Just be sure to insert dates in it.


Difference in 2 dates

Post by Peo Sjoblo » Sat, 18 Oct 2003 21:12:35

You realize that those are not really dates, there is no way excel can see a
between the date and the number 20030110, there are some workarounds though.
You could use data>text to columns to convert them to real dates, select the
click next twice and under column data format select date and YMD, click
Then just subtract like




where A2 holds the shipping date and A1 the order date, note that the DATE
function make excel format result as dates so the result will look like a
date from 1900, just re-format the cell as General and you'll get 85 in this



Peo Sjoblom

Difference in 2 dates

Post by Gerr » Sun, 19 Oct 2003 02:39:10

Thank you all for your assistance. I have successfully
solved my problem. Have a great weekend!