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

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

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)

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

between

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

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

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

said.

20030405.

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.

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

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

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

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

Thank you all for your assistance. I have successfully

solved my problem. Have a great weekend!

Gerry

solved my problem. Have a great weekend!

Gerry

1. date difference using current date is no date is entered

2. Calculate date difference between fixed date and current date

3. Add-Ins/Links/References what's the difference?

4. Add-Ins/References/Links Difference

5. difference between COM Add-ins and Plug-in

6. Difference between a driver's "post date" & "release date"

7. Date Difference; Employee Hire Date and Now; Thanks so Much!

8. Quoting ( Is there a difference between +new Date() and new Date()?)

9. Find the date difference between today's date and 1/1/04

10. Date difference and date compare

11. Q. Difference between today's date in a form, and a fixed date?

12. Difference between today's date and a past date

13. Calcuate date difference when two dates are same

14. Difference System date and Excel Date function

15. format difference of dates as date

9 post • Page:**1** of **1**