# Subtracting months from a date

#### bruty

##### Active Member
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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Jonmo1

##### MrExcel MVP
Try

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

#### Lewiy

##### Well-known Member
Try this:
Code:
``=A1-(DATE(0,7,0))``

##### MrExcel MVP
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

##### Active Member
Cheers all for the quick replies

#### bruty

##### Active Member
Try this:
Code:
``=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

##### Active Member
Try

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

Works a treat - thanks

#### Lewiy

##### Well-known Member
Yeah you’re right, scrap that. Jonmo’s is the best method I think.

#### bruty

##### Active Member
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

##### MrExcel MVP
while I don't think any method is truely perfect when dealing with dates. With my metod
Code:
``=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.

Replies
2
Views
327
Replies
7
Views
284
Replies
1
Views
313
Replies
7
Views
418
Replies
10
Views
2K

1,196,017
Messages
6,012,872
Members
441,737
Latest member
bijayche

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back