Formula - Current Week/Last Week
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
Best Answers
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!