How do I adjust my week number formula for a Sun-Sat work week instead of a. Mon-Sun wee
![Shreeraj](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
My formula I used is:
=IF((WEEKNUMBER([Planned Launch Date]@row) + 22) > 52, (WEEKNUMBER([Planned Launch Date]@row) + 22) - 52, WEEKNUMBER([Planned Launch Date]@row) + 22)
Best Answer
-
According to the help page for WEEKNUMBER, it starts the week on Mondays.
It looks like, from your formula, that if the week number is 31 or higher, add 22 to the week number and then subtract 52 (note: why not just subtract 30?), otherwise, add 22 to the week number. I assume you're trying to find the week number that is 22 weeks in the future.
You could probably accomplish this by using a helper column to alter the WEEKNUMBER results, and then use this week number in your formula. Let's call the column "WeekNum". In the WeekNum column, use the following:
=IF(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 1), WEEKNUMBER([Planned Launch Date]@row))
In English, this says if the day of the week for the Planned Launch Date is Sunday, add 1 to the Week Number value for this date (this pushes Sunday week numbers into the following week's number;) if the week day is any other value, just use the week number value for the date.
Then, replace the WEEKNUMBER([Planned Launch Date]@row in your formula with WeekNum@row, and we'll simplify the math to be more straightforward:
=IF(WeekNum@row > 30, (WeekNum@row - 30), (WeekNum@row + 22))
In English - If the week number is 31 or higher (which would mean week number + 22 is greater than 52) then subtract 30 from the week number, otherwise, just add 22 to the week number.
Note: If you wanted to avoid using a helper column and get a little crazy, we'll just incorporate all the math into the formula itself:
=IF(AND(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 23) > 52), (WEEKNUMBER([Planned Launch Date]@row) -29), IF(AND(WEEKDAY([Planned Launch Date]@row) <> 1, (WEEKNUMBER([Planned Launch Date]@row) + 22) > 52), (WEEKNUMBER([Planned Launch Date]@row) - 30), IF(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 23), (WEEKNUMBER([Planned Launch Date]@row) + 22))))
(edited to fix the all-in-one formula here at the end.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
According to the help page for WEEKNUMBER, it starts the week on Mondays.
It looks like, from your formula, that if the week number is 31 or higher, add 22 to the week number and then subtract 52 (note: why not just subtract 30?), otherwise, add 22 to the week number. I assume you're trying to find the week number that is 22 weeks in the future.
You could probably accomplish this by using a helper column to alter the WEEKNUMBER results, and then use this week number in your formula. Let's call the column "WeekNum". In the WeekNum column, use the following:
=IF(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 1), WEEKNUMBER([Planned Launch Date]@row))
In English, this says if the day of the week for the Planned Launch Date is Sunday, add 1 to the Week Number value for this date (this pushes Sunday week numbers into the following week's number;) if the week day is any other value, just use the week number value for the date.
Then, replace the WEEKNUMBER([Planned Launch Date]@row in your formula with WeekNum@row, and we'll simplify the math to be more straightforward:
=IF(WeekNum@row > 30, (WeekNum@row - 30), (WeekNum@row + 22))
In English - If the week number is 31 or higher (which would mean week number + 22 is greater than 52) then subtract 30 from the week number, otherwise, just add 22 to the week number.
Note: If you wanted to avoid using a helper column and get a little crazy, we'll just incorporate all the math into the formula itself:
=IF(AND(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 23) > 52), (WEEKNUMBER([Planned Launch Date]@row) -29), IF(AND(WEEKDAY([Planned Launch Date]@row) <> 1, (WEEKNUMBER([Planned Launch Date]@row) + 22) > 52), (WEEKNUMBER([Planned Launch Date]@row) - 30), IF(WEEKDAY([Planned Launch Date]@row) = 1, (WEEKNUMBER([Planned Launch Date]@row) + 23), (WEEKNUMBER([Planned Launch Date]@row) + 22))))
(edited to fix the all-in-one formula here at the end.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!