Current Week for Period

Cesar Perez

I'm trying to use a formula to identify the "Current Week" in my file. The file calculates the reporting period based on the date the new row is created. It also calculates the reporting week number based on the period ending date. The issue is that when I use the WEEKNUMB function to calculate the "Current Week" it does not work on Sundays. It actually leaves that cell blank. I'm using the following formula:

=IF(AND(Year@row = "Current Year", WEEKNUMBER(Added@row) -1 = [Week Number]@row), "Current Week")

https://app.smartsheet.com/b/publish?EQBCT=d6af98f68c33423bbd7c4f8139b0308e

Toufong Vang

Hi, @Cesar Perez , WEEKNUMBER() is based on Mondays. This year, January 1 falls on a Sunday so WEEKNUMBER() calculates 1/1/2023 as week 52 for 2022.

If a date falls on Sunday, you can increment it by 1 day, "+1", so that it matches your reporting period weeks.

WEEKNUMBER(IF(WEEKDAY(Added@row)=1, Added@row + 1, Added@row))

= IF(AND(Year@row = "Current Year", WEEKNUMBER(IF(WEEKDAY(Added@row)=1, Added@row + 1, Added@row))-1 = [Week Number]@row), "Current Week")

Cesar Perez

Thank you @Toufong Vang. Unfortunately your solution made all the entries in this year as "Current Week". only the period in week #2 should be "Current Week" from 1/8/23-1/14/23. The period date is based on when the row was added. The new upcoming period is 1/15/23-1/21/23. That period will be added on Sunday, 1/22/23. At that time. that row should be the "Current Week". I am stomped on this case.

https://app.smartsheet.com/b/publish?EQBCT=d6af98f68c33423bbd7c4f8139b0308e&_ga=2.157092475.1306286031.1674136873-2036790058.1649704345&_gl=1*1cdxy8z*_ga*MjAzNjc5MDA1OC4xNjQ5NzA0MzQ1*_ga_ZYH7XNXMZK*MTY3NDI0MzUwNy40NC4xLjE2NzQyNDM1MTUuNTIuMC4w