| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If a date range contains a leap year (date) | Excel Worksheet Functions | |||
| print half of rows on left and other half on right | Excel Discussion (Misc queries) | |||
| Help w/formula to add 1 year to cell (date done) date due? | Excel Worksheet Functions | |||
| Year-to-date year to date formula | Excel Worksheet Functions | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Linear Mode
