Need to add 6 months to date 2

[hahnsm]

Hey all:
I have a date format of mm/dd/yy in column B2 in excel. In C3 I would like to add 6 months to the date shown in B2. I have read a lot of threads about dates but none of them help me out. Do I just need to add the number of days located in a 6 month time period?

 

[bluedragon2]

If you want an exact 6 month increase, you can do:

IF(Month(B2)>6,DATE(YEAR(B1)+1,MONTH(B1)-6,DAY(B1)),DATE(YEAR(B1),MONTH(B1)+6,DAY(B1)))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

opps, all should be B2:

IF(Month(B2)>6,DATE(YEAR(B2)+1,MONTH(B2)-6,DAY(B2)),DATE(YEAR(B2),MONTH(B2)+6,DAY(B2)))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[hahnsm]

Thanks Blue.
I just found an easier way than your suggestion. I typed in:
=DATE(YEAR(B2),MONTH(B2)+6,DAY(B2))
This works great for me!!
Sadie

 

[bluedragon2]

Yeah, I just tried it too, thought you might get an error doing it that way, but it seems to work fine

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[macropod]

Unless you're happy for the 6-month offset to flow over into the 7th month, you need to allow for the fact that the 'end' month may have less days than the 'start' month, and that the day of the 'start' month being greater than the last day of the 'end' month. To deal with such scenarios, you need to use something like:

=MIN(DATE(YEAR(B2),MONTH(B2)+6,DAY(B2)),DATE(YEAR(B2),MONTH(B2)+7,0))

Cheers

 

[hahnsm]

Macropod:
Hey thanks for letting me know!!! I hadn't thought about that!!
Much Appreciated!
Sadie

 

[Loomah]

Just another little addition to this. If you have the Analysis ToolPak installed then you can use the EDATE function

eg =edate(b2,6)

It has the same effect as the MONTH + 6 method above!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244