Current Week

shippen

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", ""))

Kelly Moore

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

shippen

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"

Kelly Moore

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

shippen

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?

Kelly Moore

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.

Heather_Verde

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