Subtracting Dates

[jaxstraww]

I have a worksheet with two date ranges in the following formats.

20070115 & 20070117 as an example. I need to fiind the days between each
item. What we have are discount dates and pay dates. Trying to find out +/-
days that they made their discount.

 

[Pete_UK]

Assuming the two dates are in A1 and B1, put this in C1:

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

Format C1 as a number with 0 dp.

Hope this helps.

Pete

 

[LanceB]

If these are excel dates formatted as indicated below then

a1-b1

if they are numbers than

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

 

[muddan madhu]

suppose in A1 = 20070115 & B1 = 20070117
then put in C1
=DAYS360(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)))

 

[muddan madhu]

sorry

C1
=DAYS360(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)))

 

[Reitanos]

If those are actual dates that have been formatted to look like 8
digit numbers then you can just subtract to find the difference. If,
however, you have typed in 8 digit number but are reading them as
dates then you are facing much more work to manage this.

 

[Rick Rothstein \(MVP - VB\)]

You could do this...

=TEXT(B1,"0000-00-00")-TEXT(A1,"0000-00-00")

Make sure the cell is formatted as General or Number.

Rick

 

[Fred Smith]

Muddan,

Days360 will not properly subtract (or add) dates, because it assumes 30
days per month. See the other reponses for valid solutions. If you are
looking to be of help to posters, test your suggestions to make sure they
work before posting them.

Regards,
Fred.

sorry

C1
=DAYS360(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)))