Formula to calculate the previous & current week's date based on current week's Monday
Please help me with the formula to get the date and day under following condition:
If Today is Monday then it should reflect last week's date from Monday to Friday (date and day), if Today is anything other than Monday then it reflect current week date and day from Monday to Friday.
Currently I am using following formula:
=IF(WEEKDAY(Date1) = 1, Date1 - (WEEKDAY(Date1) - 2), Date1 - (WEEKDAY(Date1) - 2) + 0)
This formula changed every week
Answers
-
Would you be able to explain what it is you're looking to do a little further?
When you say "date and day", what is it that you're looking to output? Is the screen capture above the example data you're looking at, or is it an example of what you want it to show?
The current formula will always show you that week's Monday Date. You can use the TODAY Function instead of referencing a Date Cell if that would help:
=IF(WEEKDAY(TODAY()) = 1, TODAY() - (WEEKDAY(TODAY()) - 2), TODAY() - (WEEKDAY(TODAY()) - 2) + 0)
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!