Formula - Current Week/Last Week

Adriane Price

I have been using a formula in one column to capture the current week and last week based upon the created column. I am not seeing my mistake or what I am missing, hoping for some guidance please.

However, last week I noticed that the formula is capturing a year ago as current and last.

The formula I have been using: =IF(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY(1)), "CurrentWeek", IF(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY(1)) - 1, "LastWeek", ""))

I have attempted to use this formula by taking out the ones in parenthesis but then I receive blanks in the last week and still showing current in last years.

=IF(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY()), "CurrentWeek", IF(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY()) + 1, "LastWeek", ""))

And when I attempted to do it this way it all populated as blank.

=IF(WEEKDAY(Created@row) > 1, IF(AND(YEAR(TODAY()) = YEAR(Created@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(Created@row)), "Current Week"), IF(WEEKDAY(Created@row) = 1, IF(AND(YEAR(TODAY()) = YEAR(Created@row), WEEKNUMBER(TODAY()) - 2 = WEEKNUMBER(Created@row)), "Last Week")))

Adriane Price

Update: I figured it out!!

Formula used -

=IF(AND(TODAY() >= [Created]@row, TODAY() <= [Created]@row), "CurrentWeek", IF(AND(TODAY() - [Created]@row <= 7, TODAY() - [Created]@row > 0), "LastWeek", ""))

Genevieve P.

Hi @Adriane Price

WEEKNUMBER only brings back the week number, so it will pick up the same week across all years. If you have multiple years in your sheets, I would suggest adding another criteria into the beginning of your formula, looking for the YEAR:

=IF(AND(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY()), YEAR(Created@row) = YEAR(TODAY())), "CurrentWeek", IF(AND(TODAY() - Created@row <= 11, TODAY() - Created@row > 0), "LastWeek", ""))

Let me know if that works for you!

Cheers,

Genevieve

Adriane Price

Update: I figured it out!!

Formula used -

=IF(AND(TODAY() >= [Created]@row, TODAY() <= [Created]@row), "CurrentWeek", IF(AND(TODAY() - [Created]@row <= 7, TODAY() - [Created]@row > 0), "LastWeek", ""))

Adriane Price

Hello,

I am hoping someone may be able to point out where I have mistakenly written the formula. I think in the beginning the culprit is WEEKNUMBER(TODAY()) but I am not sure how to fix it.

Although I was able to get the formula to work, it appears that the formula is picking up last year for two dates (04/13/2022 and 04/12/2022). I only want the formula to pull current week (04/10/2023 - 04/14/2023) and last week (04/03/2023 - 04/07/2023) for the current year 2023.

My formula I used -

=IF(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY()), "CurrentWeek", IF(AND(TODAY() - Created@row <= 11, TODAY() - Created@row > 0), "LastWeek", ""))

Screenshot - you can see that only two dates are being picked up but the other dates are not.

Genevieve P.

Hi @Adriane Price

WEEKNUMBER only brings back the week number, so it will pick up the same week across all years. If you have multiple years in your sheets, I would suggest adding another criteria into the beginning of your formula, looking for the YEAR:

=IF(AND(WEEKNUMBER(Created@row) = WEEKNUMBER(TODAY()), YEAR(Created@row) = YEAR(TODAY())), "CurrentWeek", IF(AND(TODAY() - Created@row <= 11, TODAY() - Created@row > 0), "LastWeek", ""))

Let me know if that works for you!

Cheers,

Genevieve

Adriane Price

@Genevieve P. - Yes, thank you so much. That was another one I attempted as well but I put Year at the beginning (formula listed below) and it was giving me last week only. It worked.

=IF(WEEKDAY([Created]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Created]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Created]@row)), "CurrentWeek",IF(WEEKDAY([Created]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Created]@row), WEEKNUMBER(TODAY()) - 2 = WEEKNUMBER([Created]@row)), "LastWeek"))))