Rounding Date to Next Half Year
 

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!

Rounding Date to Next Half Year
 

On Thu, 17 Jul 2008 12:20:02 -0700, frrrrrr
wrote:

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

Rounding Date to Next Half Year
 

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

"frrrrrr" wrote in message
...
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!

Rounding Date to Next Half Year
 

Try

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

Mike

"frrrrrr" wrote:

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!