# How do I adjust my week number formula for a Sun-Sat work week instead of a. Mon-Sun wee

✭✭
edited 09/23/22

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)

• ✭✭✭✭✭✭

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!