## Difference in 2 dates

### Difference in 2 dates

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.

Gerry

### Difference in 2 dates

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

### Difference in 2 dates

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)

### Difference in 2 dates

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))

between

### Difference in 2 dates

Gerry,

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

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

### Difference in 2 dates

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

said.
20030405.

### Difference in 2 dates

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

Regards,
Felipe

### Difference in 2 dates

You realize that those are not really dates, there is no way excel can see a
difference
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
column,
click next twice and under column data format select date and YMD, click
finish.
Then just subtract like

=shipdate-orderdate

or

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))-DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(
A1,2))

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
case

--

Regards,

Peo Sjoblom

### Difference in 2 dates

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

Gerry