Make a calendar in Excel
UPDATE II: just in case you don’t read the comments, J-Walk provided a pointer to a super-deluxe version.
UPDATE: replaced a missing comma–thanks Thomas and morrisok!
Just ran across some old notes and thought I’d post them here so I don’t forget (not sure, but this probably came from J-Walk). Enjoy.
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:
1. Copy the formula text to the clipboard
2. Activate an Excel sheet and select a 7-col by 6-row range
3. Press F2
4. Press Ctrl+V to paste the formula into the active cell
5. Press Ctrl+Shift+Enter (to make it a multicell array formula)
6. Format the cells using the "Date" number format.
Voila! You have a calendar for the current month.
Related Stories
POSTED IN: best of, tips and tricks
12 opinions for Make a calendar in Excel
morrisok
Apr 13, 2005 at 7:43 pm
You were missing a comma somewhere in the formula (in the last Date formula I think). This one works:
=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)
Tyson
Apr 14, 2005 at 12:21 am
Are you kidding me? You must be a slacker if you have time to do worthless tasks like this.
Just double click on the clock in the bottom right hand corner of your screen. Poof-trick, there is your fancy calendar.
J-Walk
Apr 14, 2005 at 9:27 am
If you like that, you’ll like this version even better:
http://j-walkblog.com/old/2004/12/18/
Lifehacker
Apr 14, 2005 at 9:52 am
Instant Excel calendar
Weblog Slacker Manager’s got the monster of all Excel formulas which creates a calendar for the current month. Very handy for spreadsheets that need a lil’ date reference included. Make a calendar in Excel [Slacker Manager]…
dforester
Apr 14, 2005 at 3:44 pm
Depending on your needs, you can also use the calendar control - It creates an object rather than filling the calendar in cells, which you can wire up with VB, etc.
- View… Toolbars… Control Toolbox
- Choose the bottom-left for “More Controls”
- Choose “Calendar Control 11.0″ (or whatever version)
- Place & size it how you like.
vex
Apr 15, 2005 at 5:24 am
Why not enter the first day of the month manually and then use the Excel “fill” feature? Just drag the mouse and fill the cells with values.
Bren
Apr 15, 2005 at 8:01 am
dforester: great suggestion–I didn’t know about the Calendar Control!
vex: your suggestion will work, depending on your calendar needs. That method will make dates, but only in a column or row–not both. Also, it’s not dynamic, so it won’t auto-update when you open the sheet in the following month.
Rob
Apr 15, 2005 at 9:09 pm
OK, I gotta admit, you lost me on this one. I think this is where the generational thing starts to happen, and my lack techpertise really begins to show…
Tyson
Apr 16, 2005 at 2:01 pm
This reminds me of this mousetrap I saw in The Shaper Image.
Mark
Apr 25, 2005 at 5:07 pm
Brilliant! Didnt work!
Thanks!
Stuart Gillingham
Jul 12, 2005 at 6:07 pm
why did no-one consider doing some simple conditional formatting so that todays date is highlighted.
benji
May 26, 2006 at 11:31 am
anyone know how to make this calander work as part of a conditional formula. ie: a formula that will reference this calander to EXCLUDE days like weekends and holidays? Sort of (>if, the formula happens to calculate a day that falls on a weekend < then skip to the next weekday)?
I’m not a programer…
thanks.