Excel Calendar Trick
This is old news by some measure, but it is a timeless piece of trickery with Excel.
Make a calendar in Excel:
=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)To use it:
- Copy the formula text to the clipboard
- Activate an Excel sheet and select a 7-col by 6-row range
- Press F2
- Press Ctrl+V to paste the formula into the active cell
- Press Ctrl+Shift+Enter (to make it a multicell array formula)
- Format the cells using the “Date” number format.
Voila! You have a calendar for the current month.
Voila is right.
via SlackerManager
Feb 20th 2007
This is very, very good.
Now, how do you have this formula calculate for the remaining months of the year (not the current month)?
Mar 24th 2008
You know,
MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)) is MONTH(NOW())
and one possible simplification is to define BOM as
=DATE(YEAR(NOW()),MONTH(NOW()),1)
and absorbing the “minus 1″ in the formula, giving
{=IF(
MONTH(NOW())MONTH(BOM-(WEEKDAY(BOM)-1)+ {0;1;2;3;4;5}*7+{0,1,2,3,4,5,6}),
“”,
BOM-(WEEKDAY(BOM)-1)+{0;1;2;3;4;5}*7+{0,1,2,3,4,5,6}
)}
Perhaps some will find that more intuitive or instructive.
Mar 24th 2008
There needs to be a less than and greater than sign following NOW() in the comment above - a victim of posting HTML.
May 16th 2008
It is not working man