Date:
11 Feb 2007 Com:
4 so far
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
- Published by jason in: Excel Tips 'n Tricks
- If you like this blog please take a second from your precious time and subscribe to my rss feed!
4 Responses to “Excel Calendar Trick”
#1
¬ Nuski
February 20th, 2007 at 1:23 pm
This is very, very good.
Now, how do you have this formula calculate for the remaining months of the year (not the current month)?
#2
¬ Gates Is, You Know
March 24th, 2008 at 6:16 pm
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.
#3
¬ Gates Is, You Know
March 24th, 2008 at 6:18 pm
There needs to be a less than and greater than sign following NOW() in the comment above – a victim of posting HTML.
#4
¬ krishnan
May 16th, 2008 at 1:44 am
It is not working man
Leave a reply