Archive for the ‘via:packrati.us’ tag
Formula 1 2011 Calendar
How do I create a date based IF formula in Excel 2007?
I need an formula in Excel 2007 that will look at the current date and return in a cell how many months are left in the calendar year (less one month since we are preparing statements one month in advance). There reports are due on the 20th of the month.
So if today is 11/20/10, or past 11/20/10, but before 12/20/10 the answer is 12 since there are 12 months of reporting left to do in 2011.
If today is or is past 12/20/10 but before 1/20/11 the answer is 11 since we have the report for January and there will be 11 more reports for the year.
If today is or is past 1/20/11 but before 2/20/11 the answer is 10 and so on for one year.
I can return the current date with =today() and can do IF functions for after a certain date but get lost doing that many months.
Thanks!
=MOD(10-MONTH(NOW()-20),12)+1
Format the cell as General.
You can test this formula by replacing NOW() with A1 and entering dates in A1. (you might have to reformat the cell as General again).
F1 Formula 1 2011 ask a questions get them answered – PC X360 PS3