As posted by John Walkenbach (aka J-Walk) on Dick Kusleika's Daily Dose of Excel, Ugly Formulas...
Enter as array formula in 6 rows x 7 columns (Push Ctrl, Shift and Enter simultaneously) You may have to format as days by right-clicking the selection, choosing Format Cells, Number, Custom, entering "d" and pushing OK.
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
I couldn't believe this when I first saw it. A monthly calendar using just one formula!?
Well, it so happens I made a perpetual calendar early last year (pre-blogging days). It used LOOKUP to refer to a table in which each month's days where placed. It was a matter of selecting the correct month, then hiding days that exceed the last day of the month.
Anyway, I had a close look at it last week and thought...yeah, I can make this into just one formula too, so here it is...
No formatting is required and it can be entered pushing Enter and Ctrl but it must be entered into range A1:G6. (You can get around this by subtracting ROW() and COLUMN() so that they both equal 1 for the top left cell, so for Cell C25 this would mean ROW() becomes ROW()-24 and COLUMN becomes COLUMN()-2)
=IF(IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1)>DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)),"",IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1))
or you can use the second half of the formula,
=IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-(COLUMN()+((ROW())*7)-7)>=1,"",SUM((COLUMN()+((ROW())*7)-7)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)))+1)
and then use Conditional Formatting as below (format the font color to match the background)
Hmm, not as good but not too bad considering it wasn't my intention to make a one formula calendar in the first place. Anyway, the moral of this story is...what you thought was impossible then, might be possible now...anyone can learn some new stuff, so make the most of it ;-)
P.S. I made a simple perpetual calendar sheet to download here. Don't forget to check out Mark Wielgus's AutomateExcel blog too about the same subject. Seems that everyone's doing it :-)
Update: Here's a shorter version I wrote after some prompting on my Japanese blog.
=IF(OR(COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6<1,COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6>DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))),"",COLUMN()+ROW()*7-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-6)
Adjusting it a lot easier too. ;-)