# Simple Date formula

#### HotNumbers

##### Well-known Member

I am trying to take a date and go back few months from it and also few months ahead.

In Cel A3 I have the following date: 04/14/06 I need a formula going accross in B3,C3,D3, etc. that will calculate less then 6 month, less then 5 month, less then 4 months, .... to Plus 2 months.

Any one have any ideas.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### NBVC

##### Well-known Member
Try:

In B3:

=DATE(YEAR(\$A\$3),MONTH(\$A\$3)-COLUMN(\$M1)+COLUMN(G1),DAY(\$A\$3)) copied over

#### HotNumbers

##### Well-known Member
NBVC,

It is not working. I am trying to post my Sheet to the site so that you can see what I am trying to do

#### HotNumbers

##### Well-known Member
Here is what part of my Sheet looks like
Book6
ABCDEFGHIJK
3
4-6-5-4-3-2-1012
5Release DateMonthM -6M -5M -4M -3M -2M -1M 0M +1M +2
64/14/200643/14/20063/14/20052/14/20051/14/200512/14/200411/14/200410/14/20049/14/20048/14/2004
78/15/20064
812/12/20064
9
Sheet1

#### barry houdini

##### MrExcel MVP

I'm not sure I understand what you want. Why is C6 a month before A6 but then D6 is a year before C6?

In general to deduct months from a date you can use EDATE (available with Analysis ToolPak add-in), so to subtract 6 months from a date in A6

=EDATE(A6,-6)

......Although you may need to decide what date you want if you go back 6 months from a date like 31st August

Thank you

#### NBVC

##### Well-known Member
Just want to show that my original formula does work per your original description of the problem...see formulas in row 6 (I also include the formula using EDATE() as mentioned by Barry...which gives same results)
Book3Oct20.xls
ABCDEFGHIJKL
4
5-6 Months-5 Months-4 Months-3 Months-2 Months-1 Months-0 Months+1 Months+2 Months
64/14/200610/14/200511/14/200512/14/20051/14/20062/14/20063/14/20064/14/20065/14/20066/14/2006
710/14/200511/14/200512/14/20051/14/20062/14/20063/14/20064/14/20065/14/20066/14/2006
8
Sheet1

Replies
0
Views
191
Replies
3
Views
186
Replies
2
Views
392
Replies
3
Views
309
Replies
3
Views
50

1,136,262
Messages
5,674,709
Members
419,521
Latest member
Jasonnie

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