Rounding Date to Next Half Year

[frrrrrr]

I'm trying to compose a formula that will give me the date of the nearest
half year. Meaning, if I open the spreadsheet today I'd like to get
1/1/2009. If I open the spreadsheet sometime in January I'd like to get
7/1/2010.

I'd appreciate any help!

 

[Ron Rosenfeld]

I'm trying to compose a formula that will give me the date of the nearest
half year. Meaning, if I open the spreadsheet today I'd like to get
1/1/2009. If I open the spreadsheet sometime in January I'd like to get
7/1/2010.

Assuming by January you mean this next January (January 2009), how does 1 July
2010 compute to be the "nearest" half year.
--ron

 

[Peo Sjoblom]

That is not nearest, your are rounding up. Also in January next year why
would you return July of 2010. If you meant July 2009 then this might work

=DATE(YEAR(TODAY()),CEILING(MONTH(TODAY()),6)+1,1)

--


Regards,


Peo Sjoblom

 

[Mike H]

Try

=DATE(YEAR(TODAY()),MONTH(TODAY())+6-MOD(MONTH(TODAY())-1,3),1)

Mike