Current Week Formula Question

tmichelle068

Hi all! I have a current week formula on a sheet =IF(Week@row = WEEKNUMBER(TODAY()), 1)

For the week we are currently in, it is counting Monday as the beginning of the week and I need the week to start on Sunday. Is there a way to adjust this with my forumla?

Jeff Reisman

@tmichelle068

You'll need to account for the day being a Sunday by using the WEEKDAY function...

=IF(WEEKDAY(TODAY()) = 1, IF(Week@row = WEEKNUMBER(TODAY(+1)), 1, IF(Week@row = WEEKNUMBER(TODAY()), 1)))

(Check the color-coding on the parentheses to make sure I have them correct. )

The logic is: IF the WEEKDAY value for today is 1 (a Sunday,) then run this IF formula: IF(Week@row = the week number for tomorrow's date, 1). If the WEEKDAY value for today is not a 1 (any other day,) then run this formula: IF(Week@row = the week number for today's date, 1)

tmichelle068

Thanks Jeff! I tried that forumla and it took all the information away from all cells 🤨

Here's what I tried instead, but it still doesn't count the 19th as the current week

Jeff Reisman

@tmichelle068 Seeing your data structure helps!

What you really want to alter is the function in the Week column, so that dates that fall on Sunday reflect the week number for the following day:

=IF(WEEKDAY([Date:]@row) = 1, WEEKNUMBER([Date:]@row + 1), WEEKNUMBER([Date:]@row))

If the date in the Date: cell is a Sunday, get the week number for the next day, otherwise, just get the week number for that date.

tmichelle068

That worked! Thank you SO much!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/368730#Comment_368730

Excellent! Happy to help!