How to create a date to show the first date of the previous quarter

lydia Brissy

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,

Find more posts tagged with

Formulas

Accepted answers

All comments

Hollie Green

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

lydia Brissy

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

Nick Korna

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.