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



  • Kelly Moore
    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?


  • 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
    Kelly Moore ✭✭✭✭✭✭
    edited 08/29/22


    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?


  • 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
    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
    Heather_Verde ✭✭✭✭✭

    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!