EXCEL: How to find out is a cells date is "This Week", "Last Week" or the "-CodePudding

I have a table that has various dates in Column A ("Date Worked")

Column B ("Week Num") Lists those dates as the week number

Column C ("Week") should show if the date is for This Week, Last Week, Previous Week, Or Older or in the future.

I have tried the following formula, but it just shows everything as "Old"

=IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) < 0,
    "Future",
    IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 0,
         "This Week",
         IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 1,
              "Last Week",
     IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 2,
              "Previous Week",
              "Old"
               )
           )
      )
  )

I have also tried WEEKNUM(Now()) instead, but it made no difference.

What am I doing wrong?

Thanks in advance

CodePudding user response：

When working with standard IF functions it's easier if you first make a list a of how many options you want, because you'll need as many ifs minus 1 as options. So your options are:

Older Week
Last Week
This Week
Next Week
Future Week

These are 5 options so you'll need 4 IFS. My formula is:

=IF(B1=WEEKNUM(TODAY())-1;"Last Week";IF(B1=WEEKNUM(TODAY()) 1;"Next week";IF(B1<WEEKNUM(TODAY());"Older week";IF(B1>WEEKNUM(TODAY());"Future week";"This Week"))))

CodePudding user response：

You don't need to wrap the Week Number column in the WEEKNUM function.

=IF(
    WEEKNUM(TODAY()) - [@[Week Number]] < 0,
    "Future",
    IF(
        WEEKNUM(TODAY()) - [@[Week Number]] = 0,
        "This Week",
        IF(
            WEEKNUM(TODAY()) - [@[Week Number]] = 1,
            "Last Week",
            IF(
                WEEKNUM(TODAY()) - [@[Week Number]] = 2,
                "Previous Week",
                "Old"
            )
        )
    )
)

If you are using Excel 2019 or newer you can use the IFS function to make this easier to read:

=IFS(WEEKNUM(TODAY())-[@[Week Number]]<0,"Future",WEEKNUM(TODAY())-[@[Week Number]]=0,"This Week",WEEKNUM(TODAY())-[@[Week Number]]=1,"Last Week",WEEKNUM(TODAY())-[@[Week Number]]=2,"Previous Week",TRUE(),"Old")

If you are using Excel 365 or newer you can use the LET function to create a variable for the current week number, and then check that variable to assign the status. The SWITCH function performs much like IFS:

=LET(weekNumber,WEEKNUM(TODAY())-[@[Week Number]],SWITCH(TRUE(),weekNumber<0,"Future",weekNumber=0,"This Week",weekNumber=1,"Last Week",weekNumber=2,"Previous Week","Old"))

CodePudding user response：

Using weeknum might be critical if you have dates for different years.

If you are using Excel 365 I would suggest using a LET-formula:

=LET(
mondayDate, [@Date]-WEEKDAY([@Date],2),
mondayToday,TODAY()-WEEKDAY(TODAY(),2),
diffWeeks,(mondayToday - mondayDate)/7,
diffWeeks2,IF(diffWeeks<0,1,IF(diffWeeks>3,2,diffWeeks  3)),
CHOOSE(diffWeeks2,"Future","old","this week","last week","previous week"))

First two steps retrieve mondays of the original date and today.

Subtracting and dividing by 7 results in the diff of weeks.

To be able to use the choose function, diffWeeks2 returns

1 for future dates
2 for dates older than 3 weeks
for all other dates 3 is added, so that the correct wording can be retrieved

CHOOSE then returns the correct wording.