# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Calculate future dates at 6 month intvls...?

## ederoche

I need to create a formula that shows dates 6 months from a fixed date entered in another cell...

EXAMPLE:
Cell A1 = 01/15/2000
I want cell B1 to display the date 6 months later (07/15/2000).
I want to calculate 6 month intervals from several hundred start dates in Column A and display them in Column B.

What do I use for a formula?


Thanks,
ED

----------


## Norie

ED

Try this, copied down.

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

----------


## ederoche

IT WORKS!
Thank you!

I was trying this before 
=A1 + DATE(0,6,0)
...but was getting strange returns.

I guess it shows that I am only a part-time Excel user.   :Smilie:  

Thanks,
ED

----------


## daddylonglegs

Hello Ed,

could you have a date like 31st August 2007? What would you consider to be 6 months on from that date, as February doesn't have 31 days.

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

will give you 2nd March 2008

If you think the answer should be 29th February 2008 then you could try

=EDATE(A1,6)

If you don't have Excel 2007 then EDATE requires Analysis ToolPak to be installed. To do so, Tools > Addins > tick "Analysis ToolPak"

----------


## buzzer

Hi, how can I set the OK become RED colour

----------


## rbuthello

Hello Guys, 
I too am working on a similar problem. 
I however need a to store all the past dates in another worksheet (DateRecords!). The purpose of DateRecords! is to keep track of the past entries. It would serve as a log Book.

Example:
In this example Column A contains Vehicle Nos. Column B contains a date. I need column C to calculate the future date. The cells in Column B will be updated regularly. Every time Column B is updated I need DateRecords! to store the previous Column B data in subsequent Columns.
MainData!
   A                B                C
1499          14/7/09
1966          15/7/09
2541          24/8/08
4789          11/7/11

DateRecords!
   A                B                C               D
1499          14/1/08        14/7/08      14/1/09      
1966          15/1/08        15/7/08      15/1/09
2541          24/2/07        24/8/07      24/2/08
4789          11/1/10        11/7/10      11/1/11

Regards,
Rbuthello

----------


## Badger123

Hi

I"m trying to get the formula below to work but I just get VALUE written as a return when A1 refers to a cell that has a date entered (date formatted).

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

I am a novice at this

What am I doing wrong??

----------

