hello and welcome to this excel tips
video i am sumit bansal and in this
video i am going to show you a really
clever formula to extract the weekend
dates for any given month so here i have
the year value and i have the month
value and i have used a formula so that
it gives me all the weekend dates for
february so 5th february 2022 is a
saturday and 6th february is a sunday
now if i come here and i change the
month number then this list is going to
automatically update so now it gives me
the weekend dates in january or if i
come here and i enter three for march
then it is going to give me all the
weekend dates in march so let's see how
to do this
let me first show you a formula that can
be used in microsoft 365 and then later
i'm also going to show you the formula
that can be used in any version of excel
so for this formula i'm going to use the
workday international function now what
the workday international function does
is it takes a start date and then you
can specify the number of days so let's
say my start date is 1st january
2022
and then i can specify any specific
number of days let's say i specify 10
then this formula is going to give me
the date after 10 working days starting
from the start date so it will count 10
working days and working days for this
formula would be monday to friday
saturday and sunday would be considered
as weekend so in this case it is going
to count 10 working days starting from
first january and give me the date after
10 working days so this gives me 14th
january 2022. if you go to a calendar
you can check
and here in january it starts from here
but then it counts from monday to friday
three to eight and then from 10 to 14.
so it gives me 14 because that's the
10th day after the start date when we
have counted that many number of working
days which are 10 working days
now what you can also do in this case is
i have not specified what the weekend
should be so by default it takes
saturday and sunday as the weekend but
it gives you the flexibility to select
the weekend date so you can select
saturday or sunday or sunday monday or
just one single day now here is the
clever part you can also manually
specify what days should be considered
weekends and what days should be
considered as working days and that can
be done by entering a code in double
quotes now you do not see that as a part
of the formula so this is something
you'll have to remember
in this code when you enter 0 then that
would be considered a working day and
when you enter 1 it would be considered
a non-working day so i would have to
enter 7 digits each digit could be a 0
or a one so if i enter let's say zero
zero zero zero zero which are five
working days and then one one which are
two weekend dates and hit enter it gives
me the same date here because what i've
done is i've said saturday and sundays
are non-working if i come here and i say
that friday saturday sunday are not
working then it is going to give me 18
january because now it is only going to
have count four days as working days
what we can do in this case is we can
also flip this so i'm going to change
this to 1 1 1 1 1 0 0 what this is going
to count is
these first five days monday to friday
as
weekends or non-working days and only
saturdays and sundays are working days
so now what happens is whatever date it
gives me it will only count saturdays
and sundays and it is only going to give
me the days and saturdays and sundays so
if i hit enter it gives me fifth
february because it starts from first
january and it only counts the saturdays
and sundays it it does not count the
week days
so i'm going to use this concept in the
formula so what i'm going to do here is
let me recreate this formula
so workday international i first need
the start date so my start date would be
using the date function because i want
this formula to be dynamic so if i make
any changes in the year or month value
this formula also updates so 2022
and
the month here would be one or whatever
the value is in cell b2 and then the day
part i'm going to enter zero so what it
does is if i had entered one it would
have taken first january 2022 or if the
month is let's say two here then it
would have taken first february 2022 but
when i enter 0 it takes a day before
that so this would become 31st december
2021 but if i enter 2 here then it would
become 31st january 2022. so it is going
to take a day before and the reason i am
doing this is because there is a
possibility that first january could be
a weekend so i do not want to miss out
on it so i'm going to start counting the
number of days a day before the month
starts so that it gives me all the
weekend days in that month and now here
i need to specify the number of days now
i know that in a month there could be
could be a maximum of 10 weekend days so
there could be five weeks at a maximum
and there could be a maximum of 10
weekends so i'm going to use the
sequence formula here and i would say
give me a sequence of 10 numbers if i
select this formula and press f9 what it
shows me is a sequence of these 10
numbers so this is going to help me
generate those list of 10 weekend dates
and now in the end i am going to use
that code which was one one one one one
zero zero which means that uh the first
five days of week monday to friday our
non-working days and saturdays and
sundays are working days now if i hit
enter
you can see it gives me only
the weekend dates in the selected month
so if i come here and i hit
i make it 2 then it gives me 10 dates
but all these dates would be weekends we
can check this in the calendar
it's february when i'm recording this
video and you can see 5th 6th this
saturday and sunday similarly 12th 13th
is saturday and sunday if i come here
i'll make it 3 then we can again go and
check
in march again fifth sixth this saturday
sunday 12 13 saturday sunday now one
thing that i do not like about this
formula is as of now it's giving me the
right result but i do not want the dates
of the next one so if i select three
here it gives me few dates in april as
well because i've asked this formula to
give me 10 dates i used 10 here in the
sequence so if you do not want that we
can simply do that by using a simple if
formula so let me copy this formula here
and let me reconstruct it here and i'm
going to use a simple if formula where
my
condition would be
if
the month value of these dates so let me
paste the formula here so this gives me
those list of dates i'm going to check
the month value for each date and if it
is equal to the one i have selected here
then it's fine then give me that date so
i'm again going to paste that formula so
this is my condition if the month of the
date is the same as the selected one
then give me that date else give me
a blank and now when i hit enter it
gives me these dates and you can see
when i make the selection it is only
going to give me the dates for that
month
so you can see this is the old formula i
can delete this and this is the formula
that works so if you're using microsoft
365 and you have a sequence function
then you can easily use this formula the
way you can also use this formula is
let's say you do not want
the saturdays and sundays but you only
want some days or only one saturdays
then you can tweak this code here so in
this case if you only want sundays then
i would change this to six ones and only
one zero at the end and now when i hit
enter it only gives me the sunday
similarly you can extract any day you
want only thursdays and fridays you can
do that you only want mondays you can
extract that as well now let me show you
how to tweak this formula so that you
can use this in any version of excel
now let me show you a formula that you
can use in any version of excel so in
the formula i showed you earlier we used
the sequence function in the formula but
if you do not have access to the
sequence function then you can use the
one i'm showing right now so i'm again
going to use the workday international
formula and here the start date would be
date here with year value from the cell
month value from this cell and 0 as the
day value
and now it asks me for
the number of days now in the earlier
formula we use the sequence function so
it gave us a sequence of ten numbers
starting from one so one to ten but in
this case we do not have it because if
you're not using microsoft 365 then you
do not have any equivalent function so
what we are going to use is the row
function and i'm going to subtract 4
from it why 4 because i'm in row number
5 so 5 minus 4 will give me 1 and when i
drag this formula down when i copy this
formula down this value is going to
change so this would become 2 when i
bring this down to the next cell just
below it and as i keep going down this
would increment by 1. so this will give
me the same thing that the sequence
function gave and then finally i have to
give that code which is 1 1 1 1 1 0 0 in
double quotes and now when you hit enter
it gives you the first weekend date in
the selected month also i need to adjust
these references so it's b1 here but if
i copy this formula down then this
reference is going to increment so b1
would become b2 and b2 would become b3
so i'm going to come to this reference
select any part of it and press the f4
key and when i do that it adds these
dollar signs which means that now this
is fixed if i copy this down or copy to
the right this reference would not
change similarly i'll do it for b2 now i
can copy this formula down and you can
see
this formula works now again one thing
is if i come and change the month number
you can see that the weekend dates bill
to the next month so it gives me the
weekend dates for february as well as
march so to
only get it for february i can use a
simple if function so i'll select this
would come here if
the month value for this date that this
formula gives me is equal to the one in
this cell and again f4 to lock this
then give me that date which is again
the same workday formula else give me
black now i hit enter i copy it down and
again i have the same thing if i change
this you will see that you'll find the
same result so this is how you can
quickly extract all the weekend dates
just by using the year number and the
month number by a simple formula that's
it in this video i hope you found this
useful also if you're liking these
videos please subscribe to this youtube
channel and click on the bell icon so
that you never miss out on any new excel
tips video i come up with thank you and
have a nice day