How to count current week for the month?
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.
Best Answers

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

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)
Answers

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
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

@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)?

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

Was about to type that myself paul. Good answer

Thanks @Paul Newcome and @Ramzi . both of you are a great help.
Will try out the formula.
Have a blessed day!


I was going to add a warning, but while I was writing this, I thought I'd doublecheck 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 weekbyweek.. 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)), "")

@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.

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


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

because those days are in the same week.

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

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?

@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...
Help Article Resources
Categories
Check out the Formula Handbook template!