Make a calendar in Excel
April 13, 2005 by admin
Filed under best of, tips and tricks
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.
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)
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.
If you like that, you’ll like this version even better:
http://j-walkblog.com/old/2004/12/18/
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.
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.
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.
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…
This reminds me of this mousetrap I saw in The Shaper Image.
Brilliant! Didnt work!
Thanks!
why did no-one consider doing some simple conditional formatting so that todays date is highlighted.
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.