Subtracting months from a date

[bruty]

If I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.

eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days

 

[Jonmo1]

Try

=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

 

[Lewiy]

Try this:

=A1-(DATE(0,7,0))

 

[Aladin Akyurek]

If I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.

eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days

With the Analysis Toolpak active...

=EDATE(A1,-6)

 

[bruty]

Cheers all for the quick replies

 

[bruty]

Try this:

=A1-(DATE(0,7,0))

This one doesn't seem to work for me. Using my example of 14/08/2007 in A1, using the above B1 shows 13/02/2007

 

[bruty]

Try

=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

Works a treat - thanks

 

[Lewiy]

Yeah you’re right, scrap that. Jonmo’s is the best method I think.

 

[bruty]

If I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.

eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days

With the Analysis Toolpak active...

=EDATE(A1,-6)

Also works great, ta.

 

[Jonmo1]

while I don't think any method is truely perfect when dealing with dates. With my metod

=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

This will fail on the 31st of just about any month, and especially in august, when 6 months prior is February...

Given
8/31/2007

There is no such date 2/31/2007..
closest date is 2/28/2007 - 3 days difference, so formula results in
3/3/2007


edit -

I'll take that back, the Edate does work nicely.