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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!