Weeknumber transition from end of year to new year

HMcD

I currently utilize a formula to calculate items due this week, next week, week 3, etc. Because of the transition to the new year, the formula I use is no longer counting from January 1, 2022 & beyond.

Can someone please assist me with a formula update that will (hopefully) provide a seamless EOY transition, moving forward?

=COUNTIFS({Sheet XYZ Employee}, [Column3]3, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 1)

=COUNTIFS({Sheet XYZ Employee}, [Column3]3, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 2)

=COUNTIFS({Sheet XYZ Employee}, [Column3]3, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 3)

Genevieve P.

Hi @HMcD

We can add an IF statements around each of your formulas to account for the three different possibilities:

One Week:

=IF(WEEKNUMBER(TODAY()) = 52, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 1), COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 1))

Two Weeks:

=IF(WEEKNUMBER(TODAY()) = 51, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 1), IF(WEEKNUMBER(TODAY()) = 52, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 2), COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 2)))

Three Weeks:

=IF(WEEKNUMBER(TODAY()) = 50, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 1), IF(WEEKNUMBER(TODAY()) = 51, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 2), IF(WEEKNUMBER(TODAY()) = 52, COUNTIFS({Sheet XYZ Employee}, [Column3]@row, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = 3), COUNTIFS({Sheet XYZ Employee}, [Column3]3, {Sheet XYZ DueDate}, ISDATE(@cell), {Sheet XYZ DueDate}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) + 3))))

Cheers,

Genevieve