Simple Date formula

HotNumbers

Well-known Member
Joined
Feb 14, 2005
Messages
728
I Have a small brain block today. Can someone please help me write a simple formula

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
Joined
Aug 31, 2005
Messages
5,828
Try:


In B3:

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

HotNumbers

Well-known Member
Joined
Feb 14, 2005
Messages
728
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
Joined
Feb 14, 2005
Messages
728
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top