# Weeknumber transition from end of year to new year

✭✭

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)

## Answers

• Employee

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!