How to create a date to show the first date of the previous quarter
Hi, I am trying to find a formula that returns the first day of the previous quarter based on a date column (TODAY).
Examples (DD/MM/YYYY):
-if the date column is 02/01/2024, the formula should return 01/10/2023
-if date column is 31/06/2023, the formula should return 01/01/2023
Any help would be much appreciated. Thanks in advance,
Answers
-
=DATE(YEAR(Date@row), IF(MONTH(Date@row) <= 3, 1, IF(AND(MONTH(Date@row) > 3, MONTH(Date@row) <= 6), 4, IF(AND(MONTH(Date@row) > 6, MONTH(Date@row) <= 9), 7, 10))), 1)
-
Thank you, Hollie, for your quick feedback. This formula returns the date corresponding to the current quarter, not the previous one. How to adapt it so that:
any dates between 01/01/2024 and 31/03/2024 returns 01/10/2023
any dates between 01/04/2024 and 30/06/2024 returns 01/01/2024
any dates between 01/07/2024 and 30/09/2024 returns 01/07/2024
any dates between 01/10/2024 and 31/12/2024 returns 01/10/2024
thanks in advance for your help.
Lydia
-
Amending @Hollie Green's formula slightly:
=DATE(IF(MONTH(Date@row) <= 3, YEAR(Date@row) - 1, YEAR(Date@row)), IF(MONTH(Date@row) <= 3, 10, IF(AND(MONTH(Date@row) > 3, MONTH(Date@row) <= 6), 1, IF(AND(MONTH(Date@row) > 6, MONTH(Date@row) <= 9), 7, 10))), 1)
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
- 145 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!