number

Excel Reset Week Number Every Month - (WeekDay & WeekNum Functions Explained)



Sharing buttons:

in this video I'm going to show you how

to get week numbers for each month and

at the same time exclude weekends so for

example your week numbers for 2017 will

not look like this one but they will

start from one the moment you get to a

new month there are different approaches

that is depending on how you want to

handle the cutoff week so for example do

you consider the last days in January

and the first days in February to all be

week 5 or do you always want to start

with week 1 the moment you get to the

first of the month regardless of how you

want to do it I'll show you both ways

this is the sample data set that we have

I have the date starting from 2017 till

beginning of 2018 I'm going to show you

the two versions first of all is going

to be the week number version and second

the weekday version now what I

ultimately want to do is not to show any

weak members when we come across a

Saturday and a Sunday so that's why I

put the weekday here so this cell here

in B that's just my serial numbers these

are my dates and here as you can see the

cell value equals this one but the

custom formatting of it is showing the

week name so if I press ctrl 1 or right

mouse click go to format cells you can

see the custom formatting is associated

to that if I take it away I can see the

serial number of the dates so dates are

basically just pure numbers in Excel if

I format this date too why why why why

for example it's going to show the year

associated to that serial number if I

put mm and another two M's

I see the month name associated with it

and for days I get the week day name

associated with it and I only did that

so we can test our formula so when we

come across Saturday and Sunday we don't

see any week numbers there so what's the

difference between these two that's the

difference and that's when we come to

the end of the month in the first case

it stops and resets itself back to one

when we get to the first of the next

month in the second case it keeps the

same value and it resets once we switch

the week again and we start off on a

Monday with the first version you can

have a full week that's just one day

because your week one could just be one

day or your week five could just be one

day like in this case okay whereas with

the second method the full week here is

week four even though we arrive to the

first of the next month and then we

start from one again

okay so depending on which version works

for your report you can go with that

version so let's start here in the draft

sheet I'm gonna use the week number

formula that's a very very simple

formula all I need is that serial number

which is my date I also have the option

here to define how my week starts Sunday

Monday or so on and that's when I can

use these values here but by default

it's a Sunday so I'm just gonna go with

that and see what we get

let's push the formula all the way down

these are the weak numbers letter

associated to that date and obviously

they're just gonna go all the way up

very good and here to week 53 in this

case and then it's going to reset back

to one okay so what I want to do is to

make them reset the moment I come to the

first of the next month okay so right

here that's the first of the month I

want these to be ones and I want these

to be twos because if we think about it

if I find the current week number like I

do right now and I say minus the week

number that's associated to the first of

each month that could help me because

that would be if I'm right here that

would be 6 minus 5 that would give me a

1 right if I'm here that would be 5

minus 5 that would be 0 so all I need is

to add a 1 to the result of this and

then I get 1 2 and so on alright so

let's see if it works let's just write

it right here so I take the current week

number and I say - now week number of

the first of the month and I need a

serial numbers I need a date but because

I need the first of the month and this

in this case is the first of the month

but here I want to say - 1 1 2017 1 1

2017 so all of these need to be the

first of the month I need to create that

serial number using the days function

because that's what data is going to

return it's going to return a serial

number to the week number formula so now

the day serial number needs three things

it needs the year the month and a day

well I can use the year formula to strip

out the year from this date because year

just needs the date right so that gives

me 2017 now the month I can also use the

month formula and strip out the month

here

now for the day I'm gonna fix it right

I'm gonna fix it to one this is going to

return the serial number that the week

number formula needs and then it's going

to figure out which week number is

associated to the first of that month so

I'm going to close brackets again and

press Enter let's see what we get

we get the zeros the one two and so on

so if I just add a plus 1 here

let's see if it resets here is a five

and here's a one again and then it's a

two three four five and then a one here

again so that looks good

now all I have to do is not to show

anything when I come across a Saturday

Sunday so I need to figure out am I at a

Saturday Sunday or not and a good

formula to help me with that is the week

day function again because I'm checking

like am i did I arrive at a Saturday

Sunday I can use the F function here and

in the F function

I'm gonna check the week day weekday

requires the serial number which is this

one it's my date weekday returns a

number it's going to return like we can

see here a number through one to seven

if I go with the default but I can

control the numbers I get back so if you

look at argument number two it says

Monday would be a one and Saturday would

be a six and Sundays seven that's going

to be the result of it is going to be a

number so if I go with the second

argument and say is the weekday bigger

than five

because remember six was Saturday and

seven was Sunday so if it's bigger than

five then give me nothing back otherwise

do the week number formula here so I'm

gonna close bracket press ENTER push

this down and let's see what we get so

that looks good then we go back to four

five then you start from one again but

we could run into a problem here so if I

go all the way down to here we can see

that we have a jump from week five to

week 2 and we skip one and the reason

this happens is because of Saturday

if Saturday happens to be the first day

of the month we run into this problem

why does it happen

let me just show you the week number

associated to this date and then we can

see why the formula does this

let's pull it down to here the Saturday

Sunday this is our cutoff period for the

week number right and if Saturday

happens to be the first day of the month

we're gonna take this 14 here and deduct

13 and add 1 to it so we jumped it to so

to make this more robust I'm gonna add

another if function and check if

Saturday happens to be the first day of

the month and if that's true I'm not

gonna add a 1 to this formula

ok so I'll make the formula correction

right here when I see that it works I'm

gonna copy it to the first cell and then

all the way down okay so right here I'm

gonna add the if I need to check for if

Saturday is the first day of the month

so the formula that I can use is the

weekday function for weekday I need a

date and the date is going to be the

year that's associated to this date here

then the month that's associated to this

date here and I'm gonna fix the day

which is number 1 so that gives me the

first day of the month for this date and

now I'm gonna check if this equals a

Saturday so what return type is Saturday

well if I go with the default let's just

take a look it says one is Sundays from

7 is Saturday so if I ignore that last

argument and go with the default and say

if this equals 7 it means is this equal

to Saturday and if it is then I'm going

to use that same formula I'm just not

going to add that one to it so I'm going

to copy this paste it right here and

since I started in an if what should

happen if it's a Saturday it should do

this otherwise it should add the 1 to it

and I need to add a bracket right here

okay so this looks good and that looks

good as well

let me just check that if that stays 5

with my formula

that looks good okay so I'm gonna remove

this and copy this revised formula to

here and then push it all the way down

okay so let's just double-check this

case is now correct now I know there is

another Saturday that is the first so

let's just check for that as well and

that's this one and this looks good as

well I remembered a video from Mike

Griffin excel magic trick 783 where he

talks about date formulas and functions

towards the end of the video he is a

formula that always gives the date of

the previous Monday and I thought that

could help us because even if you're

here and we have entered the next month

the last Monday is going to be this here

so if you manage to find that last

Monday and we give all of these days

here the same number that could help us

with giving us the same week number for

cases like this and after that once we

have the same number for all of them we

need to figure out how we can switch

them to 1 2 3 4 and 5 let's start here

I'm gonna write the formula here because

we have a Monday and then we'll see how

we can adjust it so first of all let's

get the week D associated with this date

here now from the options here I'm gonna

pick this one where Monday is a 0 and

Sunday is a 6 ok so that's the number 3

what do we get well this is a Monday

right so we get a 0 I pull this down

that's the 1 2 3 and so on so here we

are 1 away from a Monday here we are six

away from and

here we are the monday again and then

we're one away so that's the information

that we can use to correct our days so

that we can get the same number here

what number do we want let's say in the

first case I want to see Monday's day

numbers I want to see two for all of

these here Monday is nine so I want to

see nine for all of these here Monday

sixteen I want to see sixteen for all of

these how do we do that

well I can use the day function get the

day associated to this date and deduct

this here so in this case what do I get

I get two right that's a day associated

to this date 2-0 I get a two and here

what would I get I get a 3-1 it's gonna

be a two here I would get a 4-2

I hit a two so if I pull this down I'm

gonna get the same numbers I'm gonna get

the Monday's for all of these dates how

do we use this information to get

instead of 16

23:36 13 27 and so on how do we

translate these two one two three four

and five I'm sure there are many

different ways of doing this and please

do share your versions one way I thought

of is to wrap this up in the week number

formula so yes I say I'm using weekday

here but in fact week number is also in

here and even in the week number formula

we are also using weekday it's just the

core driver of the formula is one or the

other so how can I do that how can I use

the week number well if I use the week

number of a date

and use this day in the day argument but

always look at the same month maybe the

same year or we can make year variable I

should get the correct week numbers

right well let's test it

I need the serial number and because I'm

splitting it up I have days separate I

need to use the date function date

function requires here so year I'm gonna

go with this one then the month I'm

gonna fix the month I'm just gonna put

one as my dummy month here so always

look at January and a day is gonna be

this

okay so let's close brackets go with the

extra bracket and let's check it out

let's see what happens when we come

across this that looks good so just to

make them look similar let's just apply

the first if function so that we don't

see any values for Saturdays and Sundays

so we keep that the same

if weekday of this we go with the second

argument where Saturdays is 6 and Sunday

is a 7 if this is greater than 5 then

nothing otherwise do the rest ok I'm

just going to pull this also one up and

let's push it all the way down that

works great in this case if you have any

other versions please do sure so these

were my two versions of getting monthly

week numbers that you can use in your

reports if you liked this video don't

forget to leave a thumbs up and for more

of these videos why not subscribe to my

channel for updates when new videos like

this one come out