WeekNumber & WeekDay error
Can anyone help with a way round the weeknum formula that starts on a Monday rather than a Sunday?
I'm using the WeekNumber formula to get a current week in conjunction with the WeekDay formula to create a list of tasks due in a current week sorted by days. Because the dates for when each task is due is auto populated based on a delivery date, sometimes the tasks end up being assigned to a Saturday or Sunday.
For example a task due on Sunday 29/05/22 is showing as a Current week task and is assigned to week day 1. Therefore it's showing in the task list for Monday 23/05/22.
Any suggestions please?
Answers
-
Hi @Nikki_Glass
Would you want the Saturday to also move to the next Monday? So the WeekNumber starts on the Saturday of the week?
This formula will start the week on the Sunday:
=IF(WEEKDAY(Date@row) = 1, WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))
You could include Saturday in the formula by checking for Weekday 7 as well as 1:
=IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))
Let me know if this makes sense and will work for you!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thanks so much that's sorted it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!