Formula - Current Week/Last Week

Options
Adriane Price
Adriane Price ✭✭✭✭✭✭
edited 04/03/23 in Formulas and Functions

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

Tags:

Best Answers

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭
    Answer ✓
    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", ""))



    Adriane

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭
    Answer ✓
    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", ""))



    Adriane

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭
    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.


    Adriane

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Adriane Price ✭✭✭✭✭✭
    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"))))

    Adriane

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!