How to count current week for the month?

Vivien Chong

I need a formula to calculate the week number for the month, for example 28 May 2020 is the 5th week of the month.

I understand that week number calculate from w1 - w52.

How can I calculate the week number for the month? So basically, the answer will be between number 1 - 5.

Thanks.

Paul Newcome

I am assuming your dates are in the format of dd/mm/yyyy, so you are wanting to automatically pull the week number for the first week of the current month? If that is correct...

=WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1

Paul Newcome

@Vivien Chong

When I replaced TODAY() in the WEEKNUMBER() - WEEKNUMBER formula with dates (to see what would happen when TODAY() becomes those dates), I actually ended up getting a fair amount of incorrect outputs including some negative numbers.

I would actually suggest using @L@123's solution of:

=ROUNDUP((WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + DAY(TODAY())) / 7)

Ramzi K

Hi @Vivien Chong,

Try this:

=IF(DAY(Date@row) <= 7, 1, IF(AND(DAY(Date@row) > 7, DAY(Date@row) <= 14), 2, IF(AND(DAY(Date@row) > 14, DAY(Date@row) <= 21), 3, IF(AND(DAY(Date@row) > 21, DAY(Date@row) <= 28), 4, 5))))

I hope this helps.

Cheers,

Ramzi

Vivien Chong

@Ramzi thanks for your prompt reply.

If I understand your formula correctly, you are defining the week number as:

Day 1 - 7 = Week 1

Day 8 - 14 = Week 2

Day 15 - 21 = Week 3

Day 22 - 28 = Week 4

Otherwise it is Week 5

However, the calendar week is different. For example, with reference to the May 2020 calendar below,

1 - 3 May = Week 1

4 - 10 May = Week 2 and so on

If we use this: =WEEKNUMBER(TODAY())-WEEKNUMBER(1/5/2020)+1

Then we will have to constantly change the date, from 1/5/2020 to 1/6/2020 to 1/7/2020.

Any advice on how to automated the "date" part (1/5/2020, 1/6/2020, 1/7/2020 etc)?

Paul Newcome

I am assuming your dates are in the format of dd/mm/yyyy, so you are wanting to automatically pull the week number for the first week of the current month? If that is correct...

=WEEKNUMBER(TODAY()) - WEEKNUMBER(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) + 1

L_123

Was about to type that myself paul. Good answer

Vivien Chong

Thanks @Paul Newcome and @Ramzi . both of you are a great help.

Will try out the formula.

Have a blessed day!

Paul Newcome

@Vivien Chong Happy to help! 👍️

@L@123 Thanks! Sometimes I have a good idea or two. Hahaha

Ezra

I was going to add a warning, but while I was writing this, I thought I'd double-check and it seems that the 'bug' has been fixed.. so you can probably ignore this.

I've noticed that some December dates will calculate as Week 1 if they are at the end of the year and "overlap" into the next year's Week 1...

for example, 12/28/20 is within the Week 1 of 2021, so it will return "1"

I needed to be able to capture certain data week-by-week.. and keep in mind that WEEKNUMBER(column@row) will give you an absolute number - I needed the first few weeks to be formatted like 02.2020 instead of 2.2020 so they would sort correctly. That's why there's the part about IF(WEEKNUMBER(..)<10

This has prompted me to create a column to build the week number with the year and check to see if the month is December:

=IFERROR(IF(AND(MONTH([Actual Start]@row) = 12, WEEKNUMBER([Actual Start]@row) = 1), YEAR([Actual Start]@row) + 1, YEAR([Actual Start]@row)) + "." + IF(WEEKNUMBER([Actual Start]@row) < 10, "0" + WEEKNUMBER([Actual Start]@row), WEEKNUMBER([Actual Start]@row)), "")

Paul Newcome

@Ezra I had forgotten about this, and I do remember it happening in the past, but you say it seems to be fixed? I haven't tested it in a while.

Ezra

https://community.smartsheet.com/discussion/comment/252098#Comment_252098

Tested it.. WEEKNUMBER of 12/28/20 returns 53

Paul Newcome

Awesome!

Paul Newcome

Except 1/1/21 also provides a week number of 53...

L_123

because those days are in the same week.

L_123

I take it back, this does not show the same behavior in 2021 as it shows in 2020. There is definitely a glitch going on

Vivien Chong

Thanks @L@123 @Paul Newcome @Ezra.

If I understand the above threads of information correctly, probably in 2021, we'll have to adjust the formula again?

Paul Newcome

@L@123 Yes. They are in the same week, but I would think that as soon as you had a 2021 date it would start back over at 1. I may have an idea, but I need to do some testing. I'll be back...