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.

Gerry
 
 
 

Difference in 2 dates

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

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

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

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

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

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

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

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
 
 
 

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

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

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

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

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!

Gerry