Create Calendar using advanced Excel formulas [video tutorial]

Apologies for the spelling mistake. It was a typo..

Learn Excel Formulas in this tutorial. This video tutorial is mainly addressed to people who want to learn complex excel formulas and want to see the use in practical examples.

This tutorial explains about creating month calendar in excel. It's for advanced excel users and for those who have some knowledge of Excel formula.

Ms Excel formula and function are widely used in this tutorial.

Rate this video to see more on this channel.

---

Closed Caption:

hello everyone welcome back
so this tutorial is about calendar which
we will be creating using Excel formulas
only we won't be using any other thing
like will be a macro door direct values
are something else will be just using
excel formula to create this dynamic
calendar you can see here we have to
spend buttons so the this pen buttons
can be added from here Developer tab
the rude and more . just go to insert
and then see spin button under activex
controls you can insert these controls
here and set their relations i'll show
you how to set relations here so just
let me first show how it works
so here if I want to take months i can
click here downwards to go back and if i
go upwards it
I can go up upside like December
let's say I want to say see calendar for
months - 1:30 so i can go back much you
can
13 now showing me the right calendar for
much
alright so this is all calculations the
all these calculations are being done
using formulas only you can see here
like this
all right lots of calculations are being
done even if the soul is blank and it
took out right
the year that kids need to the black car
we need to have some value all right
let's start with it
I'm opening a blank sheet right
so as you can see that need to have two
cells 14 months and 14 year for
selecting these so i'm creating these
first of all so I'm putting month here a
month and then here
alright thats the in and in Developer
tab insert spin button and so it has
been burning for a month and then copy
pieced inserted far here right
and in the properties of spin button
I'm right clicking it going to
properties here
the properties of spin button i can set
its maximum value minimum value right
its reference so its maximum value for a
month can be well
minimum value can be won and links
it is linked to let's say I put value
then that's 8 34
four month right
so this is not done
what problem see copies
art is done now if I again click to
design more will come out of the design
mode and now you can see if I includes
it will go well
well and further clicks one english its
value and if I go down it can go down to
one because its minimum value 1 and
maximum value of well
similarly I can set its property then I
need to go to the then more click their
set properties and let's say I put its
maximum value as where is it
I its maximum value add let's say - 200
and many more value as 1 901 whatever
you can sit here that will show her link
- so let's say five and I'm done
design
all right it is working if i change its
value to you wanted to
I can see it is moving uptown right now
based on these values
all right I can directly change its
reference divx reference to see 5 i'll
tell you the reason why i put well month
values here but for ear
it's fine it can be c5 directly
i'm deleting it
- 01 - the reference design more and it
is working fine now actually four month
I just want to shoot that and this sheet
I'm not showing you the number four
month for yes if it would definitely be
a number but for month
i'm showing you a text for that I have
put the value for that in universal but
to show up the X plus 1 into that I can
use to function
- and next number in this X values vary
from jan-feb all the value to be in
double coach
yeah
and this is it I can see if i increment
this value this value all coaching
because it's dependent on this value and
it is just showing you explore apartment
to that index
wait so this is done now I can hide
these values if you want to but simply
changing its color legit
Oh like this whatever you want to change
for the formatting
this is all right
now we have we are done with changing
month and ear part
no need to create a framework for
calendar so a simple calendar contains
this sunday monday like this
I so these will be the week days of the
month and father now
so every month can begin with
Sunday Monday either of these days
all right it is not a mandate thing that
it will always be starting with sunday
or whatever
22 right formula very smartly that
yes efforts first days sunday then only
a put one here and sunday otherwise be
with black right
like this if the first day of that month
is monday then put one here
otherwise leave with black so i'll be
using these formulas like this
so we have an axle
I like so first who we are going to
learn about a formula i have I need to
make create a date for the month and the
year that users has specified
alright so i can create a date using the
formula date and I can specify year in
it , montana that is in this cell and
let's say I'm putting in first day of
that it will be first made 2,000
maintaining all right so this is how it
is changing
not now i have first the earth that
particular date using this state
I can find the witch date was using with
the function is equal to we day off this
particular date that is five and five
mins this particular day was fifth day
off that I mean that week that is a 1 2
3 4 5
that means it is it was because it
alright so now you can use your formulas
I'm combining these formulas here with
the e3 e3 all this
I'm dropping it and putting it
static value here right now I need to
check if this particular value is 1 then
put one here right
otherwise leave it blank of this values
to then put one here that day with blank
read three-putted value here
leave it blank if it is five in like in
this case put one here
all right so like this way so i'm
starting with it
if this week day we day of this date is
equal to one then put one of the white
limited plan for this a weekday
I since the first one is John plus the
second
let's copy it
copy
based and if this particular day is
second day of month then put one here
otherwise
don't leave it blank right now
two cases come here I'm not leaving it
blank
let's see first day of that month was
one then it needs to be this plus one if
it was not one lets you select i'm
showing you it with an example let's say
the first month was one then this seller
I i was expecting to that is this
particular value plus 1
but in case if this one all the second
case is if this particular value is 1
then we can go forward right
it will be putting one here but in is
this is one
are any of these is one in that is this
new blank so there are three kids is not
to i need to put a foundation here that
if this is too
still we have two cases for this do then
it would definitely one here in the
second case which alex screen but if
that is not true
then there are two possibilities this
can be one and this any of these can be
one so i need to put two conditions here
that if the what is not leave it be
then I can directly check her if is err
be 10 plus 1 then
all right now if this is having a value
then be 10 plus 1 would be a value and
this function won't be returning a any
other but in case of that is blank and
blank plus 1
we're definitely did on the air and that
case i can leave it blank
otherwise whoever it is returning a
value like this month plus 1 it is to
entertain or returning of error then
simply add one to the number which is
previous to it
alright so this is it be 10 plus 1
otherwise leave it right there we are
done with a second formula now let's see
if this is working fine I'm not okay
yes so in this case and apples 2012
first day was Sunday and therefore
second day was monday but let's see any
of the month starting with monday
all right july is also the same yes
so this October as having for the day on
Monday and all these other days have was
the am in any of these days tuesday to
saturday so this wouldn't affect me and
we can similarly continue till saturday
just need to replace the value to the
day value here that is here that the
second day we need to just replace it
2 3 4 5 6 4 pixels
so I'm giving these formulas here like
this
three for this is something wrong
references are on my references are see
583
all right I'm writing it manually
now I need to check if this value was
three then do this we not be now add 1
to see and even see because i'm taking
its previous but you're not the first
well alright
similarly for next so if that is for
then
t same reason for this is that has five
then
e are he so now you can see in number
november two thousand twelve it was for
the first day was those days that were
all these are black and . having closed
now and next formula it should be -
let's see if it if our approach works
fine
that is six then put one here otherwise
previous cell that is as plus 1 that is
a number then put F n plus 1
yeah this is working fine similarly for
this if this is seven then g plus 1 and
g plus 1 so first line is complete
one more thing and the first day of any
weekday would definitely be having won
at least one value in first row right
it can maximum have seven also in values
are minimum it can have one value and
this particular so likewise we can move
to if if this is the first day of that
week and that month and let's say that
month is having maximum of technical
part of one days then we can go tell
here but we're starting from 1 then 18
and then 15 and then 22 then and one in
nine and we can move till this room
right so we need to write formulas down
here and we cannot ignore these formulas
so because we need to take consider all
the cases for every month I mean whether
it is time from this day are and that is
actually out whatever the day's alright
so in the next so just add this number
by one and next add 1 to it
next add 1 to it again
one to it it won't tell anybody i will
be working for
let's see where we're at
i'm adding 1 to adjust its previous
value that can be in previous cell or in
the last cell of that previous rule
alright so this is where
till what a week . what . we can you
just in a adding one
- previously because let's say from if
my month begins from sunday and that but
love month is having created this lets
it is
februari in that case create will come
here and I cannot put when I never got
that that particular month is having
only 28 days and it to have a check
beginning from this cell that if that is
a leap year and it is a februari and it
is having really it is only the limit
blank otherwise implemented so like this
we have lots of conditions we just need
to take care of all things right
so for that i'll be using a human
function
yeoman function of that particular date
calculates the last year of that problem
on like this
and here if I have it
and if i show here the date and if i use
human function E
Oh month of this date and number 20 here
that isn't it is giving me the end of
month
alright if i put 0 here then it will be
giving me
end of month off the month I mean in the
state which I'm sitting here if I bought
one here then it will be giving me the
end of month of next month
if i were to hear then the air next to
next month like this 0 like it is
showing you a number we got X represents
did in number from your Mac I just need
to change it from a crow date for mac
and then i can see the end of month
there is 30 it alright so if i change
like this and i can see that it is
giving me the end of that month in
February's you - 09 and investment
market is going to 30 or 31 to see more
logics here
try to completed yourself and you you
need to have values in next two rules
you need to write formulas still here
and try to complete it yourself if you
face any problem
are you could not complete it for any
reasons just drop a message on YouTube
video like this video
there are many options are you can
directly write to me on
excellent . my crop @ gmail.com
I'll be waiting for your mails and
definitely we can have one on one
sessions to explain your father on this
tutorial
alright see you bye
hello everyone welcome back
so this tutorial is about calendar which
we will be creating using Excel formulas
only we won't be using any other thing
like BB macros are direct values are
something else will be just using excel
formula to create this dynamic calendar
you can see here we have to spin buttons
so
this pen buttons can be added from here
Developer tab rude and more . just go to
insert and then see spin button under
activex controls you can insert these
controls here and set their relations
i'll show you how to set relations there
so just let me first show how it works
so here if I want to 10 months i can
click here downwards to go back and if i
go upward that I can move up upside like
December
let's say I want to say sequel end of
our March to hunt 30 so i can go back
much to turn in now showing me the right
calendar for march right so this is all
calculations the all these calculations
are being done using formulas only
you can see here like this right
lots of calculations are being done even
if the cells blank and it will calculate
the year that is it need to the blank or
we need to have some value right let's
start with it
I'm opening a blank sheet right
so as you can see that need to have two
cells 14 months and 14 year for
selecting these so i am creating these
first of all I'm building a month hair
month and then here
all right
in and in Developer tab insert spin
button has been burning for month and
then copy paste inserted far here right
and
in the properties of spin button I'm
right looking it when the properties
here are the properties of spin button i
can set its maximum value minimum value
right its reference so its maximum value
for month can be 12 minimum value can be
won and links it is linked to let's say
I put value then it's a three for four
months
alright so this is not done
what's the problem
see
got these
this done
now if I again like to design more to
come out of design mode and now you can
see if I increase it will go well
well and further clicks morning great
value and if I go down it can go down to
one because its minimum value 1 and
maximum value or squirrel
similarly I can set its property
I mean I need to go to the more click
their set properties and let's say
I put its maximum value as
that maximum value add let's see
- 200 and many more value as 1 901
whatever you can sit here that will show
a link to show let's say five
and I'm done
design
it is
if i change its value to do one reality
I can see it is moving uptown right now
based on these values
all right I can directly change its
reference give its reference to see 5
i'll tell you the reason why i put were
month values here but for ear is fine
it can be c 5
relating it to you know one reference
design more
and
fine now actually four month i just want
to show that in this sheet
I'm not showing you the number four
month for yes it would definitely be a
number but for month
i'm showing you a text for that I have
put the value for that in universal but
to show up the extra phone into that I
can use to function to index number is
this X values vary from Jan web
always value need to be in double quotes
and then for much apple
mom made all these months you need to
persuade you
let me complete this
and this is it
I can see if i increment this value this
value all coaching because it's
dependent on this value and it is just
showing you explore apartment to that
index
wait this is done
now I can hide these values if you want
but simply changing its college
like this whatever you want to change
for the formatting
this is
I
now we have we are done with changing
month and ear part
no need to create a framework for
calendar so a simple calendar contains
days
Sunday Monday like this
all right
so these will be the week days of the
month
and further now
so every month can begin with
Sunday Monday either of these days
all right it is not a mandate thing that
it will always be starting with sunday
or whatever you wanted to write formulas
very smartly that
yes efforts for days sunday then only
put one here in sunday otherwise leave
it blank
right like this if the first day of that
month is monday then put one here
otherwise leave it black so i'll be
using these formulas like this
so we have an axle I like the first who
we are going to learn about date formula
i have I need to make create a date for
the month and the year that users has
specified right so i can create a date
using it for Milan date and I can
specify year in it , montana that is in
this cell and lecture i am putting in
first day of that day so it will be
first made 2019
I so this is how it is changing
not now i have first day off that
particular date using this state
I can't find the witch date was using
week the function is equal to we day of
this particular date that is five and
five mins this particular day was fifth
day of that I mean that week that is a 1
2 3 4 5
that means it is it was thursday alright
so
now you can use your formulas
I'm combining these formulas there with
the e3 e3 all this
dropping it and
putting it that value here right
now I need to check if this particular
value is 1 then put one here
I other leave it blank this values to
then put one here that David plan with
three put it
value here leave it blank if it is five
in like in this case put one here right
so like this way so i'm starting with it
if this week day we day of this date
is equal to one then put one of the
widely with plan for this
if weekday I
first one is done first so second
let's copied
copy
based
and if this particular day is second day
of month
then put one here
otherwise don't leave it blank right now
two cases come here I'm not leaving it
blank
let's say first day of that month was
one then it need to be this plus one if
it was not one let you select i'm
showing you it with an example let's say
the first month was one then this seller
our i was expecting to that is this
particular value plus 1
but in case if this one all the second
case is if this particular value is 1
then we can go forward right
it will be putting one here but in case
this is one
are any of these is one in that is this
need to be black so there are three
cases not to send it to put a condition
here that if this is too
still we have two guesses but this is
true then it would be definitely one
here in the second case which alex
screen but if that is not true
then there are two possibilities this
can be one and this any of these can be
one so i need to put two conditions here
that if
the
what is not d if be
then
I can directly checker if is
beat n plus 1
then all right now if this is having a
value then be 10 plus 1 would be a value
and this function won't be returning a
any error but in case of that is blank
and blank plus one would definitely do
it on the air in that case i can leave
it blank
otherwise we have it is returning a
value like this one plus one is two and
that in order turning over error then
simply add one to the number which is
previous do it right
so this is it be 10 plus 1
otherwise leave it so here we are done
with a second formula now let's see if
this is working fine or not
ok yes so in this case in April 2012
first day was Sunday and therefore
second day was monday but let's see any
of the month starting with monday
alright july is also the same yes to
this October as having first day on
monday and all these other days have was
the am in any of these days tuesday to
saturday so this wasn't perfect and we
can similarly continue till
saturday just need to replace the value
to the day value here that is here that
the second day we need to just replace
it
2 3 4 5 6 4 x's so I'm giving these
formulas here like this
three for this
this is something wrong references are
on
my preferences rc5
83
and it and
writing it man
now I need to check if this value was
three then
do this be not be now add 1 to see and
you could see because i'm taking its
previous but you're not the first
right
similarly for next cell if that is for
then
d
same reason
for this
if that has five
then
e
r
yeah
so now you can see in number november
two thousand twelve it was for the first
day was tuesday
that's why all these are black and it is
having first now next formula it should
be - let's see if it if our approach
works fine
that is six then put one here otherwise
previous self that is f +1
if that is the number then put F n plus
1
yeah this is working fine similarly for
this if there's a seven then g plus 1
and G Plus
so first line is complete
one more thing the first day of any
weekday would definitely be having won
at least one value in first true right
it can maximum have seven
Alton values are minimum it can have one
value and this particular so likewise we
can move to if if this is the first day
of that week and that month and let's
say that month is having maximum of
article 31 days then we can go tell here
if it is starting from 1 then 18 and
then 15 and then 22 then one in nine and
we can move till this row right so we
need to write formulas stand here and we
cannot ignore these formulas so because
we need to take the consider all the
cases for every month I mean whether it
is dying from this day our is at
whatever the days right
so in the next cell just add this number
by one and next
add 1 to it next add 1 to it
again
one to it
and one hotel end but yeah it will be
working for
let's see where to where
i'm adding 1 to adjust its previous
value that can be in previous cell or in
the last cell of that previous rule
alright so this is where
till what of with . what . we can use
just in a adding one
- previously because let's say from if
my month begins from sunday and that but
Lamont is having created these
legislators of februari and that is when
it will come here and I cannot put 29
here because that that particular month
is having only 28 days
I need to have a check beginning from
this cell that if that is a leap year
and it is a februari and it is having
really it is only the lid blank
otherwise implemented so like this we
have lots of conditions we just need to
take care of all these right
so for that i'll be using human function
human function of that particular date
calculates the last year of that
procurement like this here if I have a
date and if i show here the date and if
i use human function E
oman after this date and number 20 here
that isn't it is giving me the end of
month
alright if i put 0 here then it will be
giving me
end of month off the month I mean in the
date which I am sitting here if I put
one here then it will be giving me the
end of month of next month
if i were to hear then the next to next
month like this 0
all right it is showing you a number
because X represents did in number from
the mac i just need to change from a
crow date for mac and then i can see the
end of month
there is 38 alright so if i change like
this and i can see that was giving me
the end of that month in February's you
- 29 and investment is going to 30 or 31
to see more logics here
try to completed yourself and you you
need to have values in next two rules
you need to write formulas still here
and try to complete it yourself if you
face any problem or you could not
complete it for any reasons just drop a
message on YouTube video like this video
there are many options are you can
directly write to me on
excellent . mikroc @ gmail.com
I'll be waiting for your emails and
definitely we can have one on one
sessions to explain your father on this
tutorial
alright see you bye