# Current Week

I'm working on a project where I need to identify Current Week and Last Week. In the below screenshot 08/28/22 is a Sunday (1). 08/28 - 08/31 should show as Current Week but 08/28 shows as Last Week. I've tried to change several things but can not get Sunday to become part of the current week. Any thoughts on what I doing wrong?

=IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY(1)), "CurrentWeek", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY(1)) - 1, "LastWeek", ""))

Tags:

• ✭✭✭✭✭✭

Hey @shippen

TODAY(1) indicates tomorrow (literally meaning TODAY() + 1).

The formula you are looking for is

=IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()), "CurrentWeek", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 1, "LastWeek", ""))

Will this work for you?

Kelly

• When I remove the "1" from Today(1) it make 08/28 "Current Week" but it also changes the rest of the current week to "Current Week" See below. 08/30 and 08/31 become "Next Week". 08/28, 08/30 and 08/31 should all be "Current Week" and 08/25, 08/26, 08/27 should all be "Last Week"

Sorry I have added some functionality since my original post so this looks a little different.

=IFERROR(IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()), "CurrentWeek", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 1, "LastWeek", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) + 1, " NextWeek", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 2, " > 1 Week", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 3, " > 1 Week", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 4, " > 1 Week", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 5, " > 1 Week", IF(WEEKNUMBER(PlannedComp@row) = WEEKNUMBER(TODAY()) - 6, " > 1 Week", "")))))))), "Not Planned"

• ✭✭✭✭✭✭
edited 08/29/22

Hey

The function WeekNumber starts on Mondays. That means (assuming your time zone hasn't crossed midnight yet) that from 8/22-8/28 (today for me), it is still Current Week. 8/29 starts a new week and is Next Week. Beginning tomorrow, 8/29 will become the 'Current Week'.

What results were you expecting?

Kelly

• Thanks, that what is happening. I'm used to Sunday being the fist day of the week. Is there any way to have Sunday the first day of the week?

• ✭✭✭✭✭✭

No not using Weeknumber alone. You might be able to wrestle it with a IF/and using WeekDay. The formula would be long considering all the things you’re tracking (you could shorten your formula above since a few of your responses are the same). If you’re doing it only because “it’s what you’re used to “ you may consider just using WeekNumber alone as it is much more straight forward.

• ✭✭✭✭✭

Maybe add a helper column that adds 1 to the day to move the Sunday date to Monday?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!