# Formula - Current Week/Last Week

Options
✭✭✭✭✭✭
edited 04/03/23

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

Tags:

• ✭✭✭✭✭✭
Options

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

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

Options

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

• ✭✭✭✭✭✭
Options

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