Excel Magic Trick 907: How To Make Excel Calendar (4 Examples)

Download workbook: http://people.highline.edu/mgirvin/ExcelIsFun.htm
See how to create Dynamic Excel Calendar that changes based on a date types into a cell or the current date from the TODAY function using a non-array formula:
1. Excel Calendar using Formulas and Conditional Formatting for Excel 2003 based on date typed into cell
2. Excel Calendar using Formulas and Conditional Formatting for Excel 2007 or 2010 based on date typed into cell
3. Excel Calendar using Formulas based on date typed into cell
4. Excel Calendar using Defined Name Formulas based on TODAY function
5. See the functions: COLUMNS, ROWS, WEEKDAY, EOMONTH, MONTH and IF
6. All examples are non-array formulas.

How to make a calendar in Excel 2010, 2007, 2003

---

Closed Caption:

welcome to exile magic number 907
hey if you want to download this
workbook click on the link below the
video
now this video we want to create a
calendar so here's a calendar no matter
what date I type here control semicolon
for today's date
it shows me the current calendar so if i
cant 1 / ice last 1998 whatever date i
put their I want to see that calendar
all right actually there are some great
formula is out there John walking back
has a great formula for a calendar
Bob homeless just published a book excel
outside the box with a great formula for
a calendar but here's my take on it
now we'll create our formulas down here
but we're going to need some inputs for
these formulas and i'm going to put them
up in the cells if you wanted to put
them inside the formulas that would work
also but we're going to need from any
date we type here
the first of the month we're going to
need to know what day of the week is the
first
so for februari 2012 the first day is
wednesday right so we're going to have
to figure out 123 that it's the fourth
so sunday will be one monday will be 2
etc and then we're also going to need to
know the month
februari is to write to get first of the
month
i'm going to use the end of the month
function now this is a great function in
two thousand seven and ten its default
in earlier versions you have to go to
the Tools menu add in and add the data
analysis tool path once you have the end
of the month
you simply give it a start date and end
of the month by the way is great for
getting into the month or the first of
the month
all right put in a serial date , months
if I put a 0 gives me the end of this
month which would be the 29th because it
sleep here
11 giving the end of next month and
minus 1 gives me the end of last month
well I know that
last month I simply add one and there I
have the beginning of this month now
weekday one for sunday 2-4 monday etc I
just use the weekday function
the default I can leave this argument
off because it'll just give me one for
sunday 2-4 monday etc and boom there it
is the fourth
so it tells me this calendar needs to
start at wednesday and finally we are
going to need the month - I'm gonna see
the month of that and it delivers a
number 122
now before we do our formula let's think
about how we would do this by hand
ok so there is the first
now what if i took the serial number and
added 0 here and then here i took the
serial number and added 1 and here i
took the serial number and added to
and then somehow if i could build a
formula that would take that start
dating at 0 1 2 3 down here what had
have to add 4 so the point of that is is
actually to look at because the form
i'll get a little bit complicated but
what if we could get 0 on two three and
then a four or five etc if we could
create a formula that will create that
sequence of numbers we could simply take
it and add it to that and boom we'd have
our calendar
all right another thing about this is
think about how many rows we need here
seven days
for weeks I'd be 28 right but just
because there might the the month might
start a Saturday or Friday
you need to have an extra row before and
after so you really need six rows
another thing about this is that how
many total cells are in our calendar
42 so we're going to start by seeing if
we can get a number incrementer to go
one all the way to 42
we're going to start with the columns
columns just celts columns and since I'm
in setting in cell c7
I'm going to lock the column c 7 : c 7
12 columns counts columns right now it's
counting 1 C is locked
c is not locked here but right now it's
County one when it moves over that see
will turn to addy so it increments the
number 127 you could see it's an
expandable range c 2 h is six if i copy
this down i'm going to get the numbers
127 and every row now really I like 127
and then eight nine ten
so I'm going to add to it kind of the
opposite of columns the Rose function
and i'm going to say see dollar signs
seven notice I'm locking the row
reference here instead of the column so
when I copy this down seven is locked
here but not so little as I copy down
give me one
one in this row 2 3 etc
I know this looks kind of a little bit
weird here but hang with me here so it's
adding one each time we go down which is
ultimately what we want but i want to
add 0 in the first row then one then -
no problem i'm going to take this rose
well it's giving me one of the first row
so i just subtract one now it's adding 0
now wait a second what's going on here
so -
if i add want to get to 36 if I had want
to get 27 well I really don't want one I
want to be adding seven each in each
successive row so all the tools i
multiply this notice it 0 in the first
row 0 times seven is adds 0 but down
here
it'll add 7 to every single number
control enter and there we have our
numbers 1 2 42
now that's not quite what we want right
number we wanted to 0 1 2 3 4
that's why we did weekday weekday is
perfect if it starts here we simply take
this formula subtract four well what's
for - 40
what's 5 - 4 1 2 3 4
now we're going to get some negatives
and some big numbers down there but no
problem we'll fix that
subtract weekday for and be sure to hit
the f4 key to lock it
control-enter copy over and down apps
lutely beautiful look at that this is
really the heart of this for me all
right
01 - these are all down to all the way
down to 28 because what's one plus 28 29
the last of the month this is leap year
here right now we'll fix what's up with
a formula element or with conditional
formatting will turn these off so we
don't seem a little
only see these last thing we need to do
here to this is add the first of the
month
f4 to lock it in all directions
control-enter how to be kidding me look
at this
there is the dates for our calendar
again will turn those off on those off
now before we show you how to turn those
off i want to do some formatting here no
want to add a label up here some say
equals and actually emerged in center of
these instead of center across selection
cut center across selection in 2010
causes some formatting problems but here
I want to show
whatever the name of the month in the
year so i'm going to click on that
self-control
1 2 show format cells and the custom
number for my they did was
mmm yyyy you actually only need three
wise and it shows you the actual name of
the month in the year
click OK also down here I want to show
just the day you know if you want to
show the dates like that it's perfectly
all right but i'm going to change this
before I do either conditional
formatting or formula to hide these i
just want to show the day the number
control one come down to custom and d
now i'm going to show you three methods
i want to copy the sheet over three
times and then hide all of these right
so I'm gonna point to the sheet
hold ctrl and click and drag notice that
plus and that little black triangle that
plus means I'm copying you let go of
your mouse not control
I'm going to do it again holding ctrl
let go of your mouths and finally one
three of them all right so now i'm going
to come here first going to do with
conditional formatting that works in all
versions so alt OD is conditional
formatting and actually what what is it
that we're going to do to turn off this
we need some logical test
underneath is a serial number we can ask
if any of these serial numbers are
februari or are not
february's i'm going to highlight all
these the active cell remember that
number in the cell is not really what's
there it's the serial number underneath
active cell right there is the keyboard
shortcut
all 20 d new rule and here i'm simply
going to ask equals month of the active
cell dollar signs we do not want them we
want a relative cell references copy
down and over so i hit the f4 key three
times
is that not less than greater than not
this month up here
this will give is true or false or true
wherever it's not fairy so I come over
here and in 2003 or all versions you can
change the font color to white
click ok click ok click ok so then you
can see it works and of course it is
dynamic if I changes to the next month
it changes all right let's go over to
this next sheet here
this is the method i prefer it works in
two thousand seven and ten only fault
OD new rule
same thing equals month
I should've stole that formula of this f
43 x naught
and then come over here and there's a
number tab in two thousand seven and ten
come to custom there are four sections
and custom number formatting separated
by semicolons i'm gonna type 3 123
the last section is past the third
semicolon by putting nothing
it'll show nothing you know there's
something there click ok so when it's
not favor way it will show nothing
and again this method is dynamic also
finally controls it we could do it with
a formula this little bit is delivering
the serial number
we're still going to use our month trick
right i'm going to copy that
i'm going to say give month of that
serial number is equal to and lock it
with the f4 key if thats that only comes
out true or false if it's true
I want to control be that little formal
element that's creating our serial
number
otherwise the value false double quote
close parentheses
control-enter drag it over and down
now this when would you use one of the
other
if for some reason your formula needs to
count
I use that number in some other
calculation then this is preferable
right because these cells have blank
system whereas over here
that number is still there in the cell
is just we have applied a formatting to
not show it here
there's an old text string text this is
a number so that's the difference
now there's one other task I'd like to
show you sometimes you don't want any of
this in the cells a further you want
your calendar to automatically update
and just when it is next month you just
open it and it's showing you don't want
to be in the cells so I'm gonna on this
sheet 907 for i click with my mouse i
hold control i drag
oops click and drag that plus means i'm
copying and I let go of my mouse click
not the control key there we have our
copy cheap
now again the ideas we want to be able
to delete these we look at this formula
totally connected we deleted now we get
a reference our error
so we need to simulate these formulas
the first day of the month
the weekday and the month using define
name so I'm going to ctrl f3
I've already created two of them we're
going to look at fom for first of month
first all I did is I took that formula
from our cell right
first day of the month instead of
linking it to a serial number in a Cell
I use the today function that'll
automatic its volatile automatically
give us today's date
so when I open this workbook next month
in May
it will deliver maze date and this will
give us the first of may notice
fom that's a defined name now let's look
at w day that's for the weekday here I'm
have a defined name using a function
week date and another defining
so define name is allowed to look at a
defined name right so that will deliver
the weekday
click OK we still need the month so i
click new four-month i'm gonna type C so
capital c em oh and so for current month
and i'm going to use equals month and
our f 0 be so again this is a defined
name using a formula with the function
month and the define name
fo b click ok click close is that right
ctrl f3
it's not fo be its fom now the way how
did I know that before I click notice it
didn't pop up to capital letters
if it was a defined name it would so i'm
going to click Edit fo em now watch this
when i click ok so now you can see it
popped up about capital there so it
recognizes it as a defined name
close now we need to disconnect this
form of from these cells that come over
here
this is our weekday so i'm using the
define name
i just typed WD notice functions have
this icon to find names have a dog tags
i type
WD tab this one is f 0 dog tag tab
this is the month we'll see em tab week
they also just w tab and this one's f 0
tab all right i got my fingers crossed
now we've deal inked it from the sales
control-enter April right
April's of this 2012
sunday is the first copy this over and
down the formula is working everything
up here as numbers this has blanks
we need two equals fom enter all right
and so now when we delete this right
click delete
right click delete we have a dynamic
calendar and when we open it next month
it'll show next month's calendar
all right that's a lot about creating
calendars we started use this formula
here
we also have to use use our base formula
and use conditional formatting
all right we'll see you next trip