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

Shreeraj

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)

Jeff Reisman

@Shreeraj

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

Jeff Reisman

@Shreeraj

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