🔍
Excel Calendar with Just ONE Formula! - YouTube
Channel: Leila Gharani
[0]
In this tutorial, you're going to learn
[2]
how to create a yearly
interactive calendar in Excel,
[6]
it works for Google Sheets as well.
[8]
You can use the calendar as a template,
[10]
update the year and get
the calendar for that year.
[13]
Best thing though, about
this Excel calendar
[16]
is it just needs one formula to create it.
[20]
I know you were thinking must
be horrifying, crazy formula,
[24]
we've had those on the channel,
but let me just show you.
[29]
(gentle music)
[33]
Okay, so this is where we're
going to create our calendar.
[36]
What we want is input the year here
[38]
and all our dates update
according to that year.
[42]
So first thing we're going
to need are our headers,
[45]
which are our weekdays.
[46]
So let's start off with Sunday
[49]
and let's just pull this
over until Saturday.
[53]
So now what we're going to do
is write one single function,
[56]
that's going to spill
and get us everything,
[58]
but we're going to do it step-by-step.
[62]
The function we need here
is the sequence function.
[65]
All this need is the number of rows,
[67]
it should spill to, the number of columns,
[70]
the start number and the step.
[73]
So the number of rows is clear, right?
[75]
We want our results to spill to 53 rows.
[79]
Number of columns is clear to it's seven,
[82]
we have seven days in a week.
[84]
The tricky part is this start
[86]
because the number that
ends up below Sunday
[89]
has to be a date that belongs to Sunday.
[93]
Now it doesn't have to be
the first Sunday of the year
[96]
because take a look at our 2020 calendar.
[99]
The first Sunday of the year
is actually in the second row.
[102]
The first Sunday on the calendar
[105]
belongs to the previous
year, it's the 29th.
[108]
So to keep things simple for now,
[111]
I'm just going to add
a placeholder number,
[113]
I'll just put a one.
[115]
We're going to figure
this out in a second.
[117]
What step, what step is the number
[119]
we want to increment this by
[121]
and we want to go a day at a
time, so it's going to be one.
[124]
Now both of these last two
arguments are optional,
[128]
so if I ignore them, one is the default.
[131]
I'm just putting it in
so that it's obvious
[134]
what we're doing here.
[135]
And take a look at this, I press enter
[137]
and I get my numbers all
the way down for 53 weeks.
[142]
Okay, so now let's take a
look at the tricky part.
[146]
I put a one as a placeholder,
[148]
but we need to find the correct date.
[150]
Let's just put a dummy date here.
[153]
I'll just use the date function
[155]
and assume that this is
the 1st of January, 2020.
[160]
For year, let's just select this,
[163]
then we need the month,
[164]
so we're going to put one for
January and one for the day.
[169]
Now, when I close the
bracket and press enter,
[171]
I get the number value that's associated
[175]
to the 1st of January, 2020.
[177]
If I switched this to a date,
I see my date correctly.
[183]
So we can use the date function here,
[186]
but we need to figure out the correct date
[188]
because currently this is incorrect.
[191]
1st of January belongs to a Wednesday.
[194]
So how could we figure
this out in a dynamic way?
[197]
Well, let's do this to decide up here.
[200]
We know that we can use the date function
[202]
to get the first of that
year, just like we did before.
[207]
We need to figure out what weekday
[209]
is associated with this date.
[211]
Here I can use the weekday function.
[214]
All I need is my date here,
close bracket press enter.
[218]
Now what does four mean?
[220]
Well, four means Wednesday.
[222]
How do I know that?
[224]
Under return type, which
is an optional argument,
[227]
one means Sunday, seven means Saturday.
[231]
That's the default, if
we skip this argument.
[234]
If you wanted one to be a Monday
[236]
and seven to be a Sunday,
[238]
you can go with this option or
you can go with number three,
[243]
if you want Monday to be a zero, right?
[245]
So you have different
options here, when I skip it,
[248]
it means go with the default,
[251]
but I need to somehow figure
out the date for Sunday.
[255]
Well, all I need to do is take
the first day of the month
[259]
and deduct the four here.
[263]
Now, does that do the job for me?
[265]
I get a date back is the 28th of December.
[269]
Well, if I go four back,
I'm going one extra day.
[273]
So I have to adjust it by one,
[275]
so I'm going to add a one
and that's the first Sunday
[278]
that's going to show up on our calendar.
[281]
So let's combine these
formulas into a single formula.
[285]
Copy this one in this
formula here's instead of K2,
[291]
I'm going to use the function I used.
[293]
Now let's also take the date function
[296]
and copy it in this one, so instead of J2,
[301]
we're going to paste
then the date function.
[304]
I don't need all of these,
[306]
I'm going to copy this,
go to my formula here
[311]
and paste this as my start date.
[315]
Now, press enter we have
the correct date for Sunday.
[319]
Let's update our formatting
for the rest as well.
[323]
Let's go to custom number formatting.
[325]
So I'm going to press Control + 1
[327]
to bring up the number formatting.
[329]
We can do our own custom format,
[332]
or we can go to date
and pick a shorter date,
[335]
'cause this is just too
long for our calendar
[337]
and it's not necessary to show the year.
[340]
So I'm going to go with this
one here, click on okay,
[344]
and that's that.
[346]
Let's update the size
of our columns as well.
[350]
Okay, so, so far so good.
[353]
It would be nice to add some
formatting to this as well,
[356]
so first thing I want to do is add borders
[360]
to these just light gray borders.
[362]
So let's go two more borders,
[364]
pick a gray color and added it inside
[368]
and outside and go with, okay.
[371]
Now next thing I'm going to do
[372]
is to add conditional formatting,
[374]
because I want to make the dates
[376]
that don't belong to this
year in a lighter color.
[380]
With this area highlighted,
go to conditional formatting
[385]
and add a new rule.
[387]
I want to use a formula and that formula
[390]
is to check this value here
[393]
and compare it to the
year of our date here.
[398]
So I'm going to go with does
not equal to the year function,
[404]
and we want to look at this,
[406]
but we need to take away the dollar signs
[408]
because we want this to be flexible.
[410]
We want it to look at
each single date here
[413]
and not at the dates, just sitting in B5.
[417]
D2 we want to fully fix, now
let's go and format these
[421]
to be a light gray color.
[423]
So just the font, I'll go
with this one click on okay.
[428]
Okay and now it looks
better when I scroll down,
[432]
these ones are also great at, okay.
[435]
So another thing I want to
do to be able to distinguish
[439]
the different months, let's add a color
[442]
to every other month.
[444]
So with this area, highlighted,
[446]
go back to conditional
formatting and add another row.
[450]
Now I need to figure out a formula
[452]
that's going to help me
identify every other month.
[456]
A great one that can help me
do that is the mod function.
[459]
This is going to give me the remainder,
[462]
after a number is divided by a divisor.
[465]
So in this case, my number
is actually my month number.
[470]
So if I take a look at this date here
[472]
and again I want to take
away the fixing here,
[475]
but I don't want to look at the date.
[478]
I want to look at the
month that's associated
[480]
with the state.
[482]
So I have to put this
inside the month function.
[486]
Now my divisor I'm going to go with two
[489]
because in the first
case, my month number,
[492]
let's see when it's January,
my month number is a one.
[495]
When my divisors is a
two my remainder is one,
[498]
so it means that I can
format this differently.
[502]
In the next month my month number is two.
[504]
When my divisor is two there
is no remainder it's zero,
[507]
and the pattern continues.
[510]
So I have a separate video
covering the mod function
[513]
in more detail.
[514]
I'm going to link it into
description and also in the cards.
[518]
Now let's go ahead to
format, go to the fill color,
[521]
highlight these in a different
color, click on okay.
[525]
Okay, and we have our formatting
automatically applied.
[531]
Now, if we don't want the
formatting to be applied here,
[535]
what we can do is update our
conditional formatting rules.
[539]
So let's go to manage rules,
[541]
let's just change the order of this,
[543]
we want the gray part to be first.
[546]
And then if it's true,
we want the rule to stop.
[549]
So we don't want this one to run anymore,
[552]
let's click on okay, now
this part should disappear.
[557]
Okay, so now let's test this.
[559]
I'm going to change this to 2021
[562]
and Friday is the 1st of
January, I can double check here.
[567]
My calendar is fully interactive
[571]
and it updates automatically.
[573]
The only function it needed was this one.
[576]
That was our interactive,
yearly calendar in Excel.
[579]
You can also use the same
formula in Google Sheets.
[582]
This is why I'm so excited
about dynamic arrays
[585]
a simple function that spills
like the sequence function
[589]
can be supercharged to become a template
[591]
for a yearly calendar.
[593]
If you'd like to learn more
about these new functions,
[596]
your Microsoft 365, check
out the playlist I have here.
[601]
Linked to it is in this
description as well.
[603]
If you'd like to learn all
of this in a structured way,
[607]
whether it's Excel or Google Sheets,
[609]
check out my courses on xelplus.com.
[612]
It's great to have you here.
[614]
Thank you for watching till the end.
[616]
Don't forget that thumbs
up before you leave.
[618]
Subscribe if you haven't
[619]
and I'm going to see
you in the next video.
[622]
(gentle music)
[626]
♪ Yes ♪
Most Recent Videos:
You can go back to the homepage right here: Homepage