Writing Quarter formula with different years

Felicia Nabors

I am trying to write a quarter formula which includes quarter for the next year. For example, I would my results to return Q1 2023, Q2 2023, Q3 2023, Q4 2023, Q1 2024, etc.

I am completely stumped. Has anyone done this before and if so, how did you make it work.

TIA.

Felicia

Kelly P.

@Felicia Nabors

Try this:

=IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")

Hope this helps!

Danielle Arteaga

Hi, Felicia. Are you trying to determine a quarter based on a date?

I do this on my sheets in separate column, using a nested IF formula that include the date ranges for my quarters. Here's the formula from my sheet. You can substitute the [column_names] and dates with you own and adjust to work before Q1 FY21 or beyond Q1 of FY24 as you need. You can also edit the quarter name (in quotations) to format as you like (e.g., Q1 2023 instead of FY23Q1 as I have it)

=IF(AND([RR Target Start Date]@row >= DATE(2020, 7, 26), [RR Target Start Date]@row <= DATE(2020, 10, 24)), "FY21Q1", IF(AND([RR Target Start Date]@row >= DATE(2020, 10, 25), [RR Target Start Date]@row <= DATE(2021, 1, 23)), "FY21Q2", IF(AND([RR Target Start Date]@row >= DATE(2021, 1, 24), [RR Target Start Date]@row <= DATE(2021, 5, 1)), "FY21Q3", IF(AND([RR Target Start Date]@row >= DATE(2021, 5, 2), [RR Target Start Date]@row <= DATE(2021, 7, 31)), "FY21Q4", IF(AND([RR Target Start Date]@row >= DATE(2021, 8, 1), [RR Target Start Date]@row <= DATE(2021, 10, 30)), "FY22Q1", IF(AND([RR Target Start Date]@row >= DATE(2021, 10, 31), [RR Target Start Date]@row <= DATE(2022, 1, 29)), "FY22Q2", IF(AND([RR Target Start Date]@row >= DATE(2022, 1, 30), [RR Target Start Date]@row <= DATE(2022, 4, 30)), "FY22Q3", IF(AND([RR Target Start Date]@row >= DATE(2022, 5, 1), [RR Target Start Date]@row <= DATE(2022, 7, 30)), "FY22Q4", IF(AND([RR Target Start Date]@row >= DATE(2022, 7, 31), [RR Target Start Date]@row <= DATE(2022, 10, 29)), "FY23Q1", IF(AND([RR Target Start Date]@row >= DATE(2022, 10, 30), [RR Target Start Date]@row <= DATE(2023, 1, 28)), "FY23Q2", IF(AND([RR Target Start Date]@row >= DATE(2023, 1, 29), [RR Target Start Date]@row <= DATE(2023, 4, 29)), "FY23Q3", IF(AND([RR Target Start Date]@row >= DATE(2023, 4, 30), [RR Target Start Date]@row <= DATE(2023, 7, 29)), "FY23Q4", IF(AND([RR Target Start Date]@row >= DATE(2023, 7, 30), [RR Target Start Date]@row <= DATE(2023, 10, 29)), "FY24Q1", "OTHER")))))))))))))

Kelly P.

@Felicia Nabors

Try this:

=IFERROR(IF(OR(MONTH(Date@row) = 1, MONTH(Date@row) = 2, MONTH(Date@row) = 3), "Q1" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 4, MONTH(Date@row) = 4, MONTH(Date@row) = 6), "Q2" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 7, MONTH(Date@row) = 8, MONTH(Date@row) = 9), "Q3" + " " + YEAR(Date@row), IF(OR(MONTH(Date@row) = 10, MONTH(Date@row) = 11, MONTH(Date@row) = 12), "Q4") + " " + YEAR(Date@row)))), "")

Hope this helps!

Felicia Nabors

Thanks

Danielle Arteaga

Hi, Felicia. Are you trying to determine a quarter based on a date?

I do this on my sheets in separate column, using a nested IF formula that include the date ranges for my quarters. Here's the formula from my sheet. You can substitute the [column_names] and dates with you own and adjust to work before Q1 FY21 or beyond Q1 of FY24 as you need. You can also edit the quarter name (in quotations) to format as you like (e.g., Q1 2023 instead of FY23Q1 as I have it)

=IF(AND([RR Target Start Date]@row >= DATE(2020, 7, 26), [RR Target Start Date]@row <= DATE(2020, 10, 24)), "FY21Q1", IF(AND([RR Target Start Date]@row >= DATE(2020, 10, 25), [RR Target Start Date]@row <= DATE(2021, 1, 23)), "FY21Q2", IF(AND([RR Target Start Date]@row >= DATE(2021, 1, 24), [RR Target Start Date]@row <= DATE(2021, 5, 1)), "FY21Q3", IF(AND([RR Target Start Date]@row >= DATE(2021, 5, 2), [RR Target Start Date]@row <= DATE(2021, 7, 31)), "FY21Q4", IF(AND([RR Target Start Date]@row >= DATE(2021, 8, 1), [RR Target Start Date]@row <= DATE(2021, 10, 30)), "FY22Q1", IF(AND([RR Target Start Date]@row >= DATE(2021, 10, 31), [RR Target Start Date]@row <= DATE(2022, 1, 29)), "FY22Q2", IF(AND([RR Target Start Date]@row >= DATE(2022, 1, 30), [RR Target Start Date]@row <= DATE(2022, 4, 30)), "FY22Q3", IF(AND([RR Target Start Date]@row >= DATE(2022, 5, 1), [RR Target Start Date]@row <= DATE(2022, 7, 30)), "FY22Q4", IF(AND([RR Target Start Date]@row >= DATE(2022, 7, 31), [RR Target Start Date]@row <= DATE(2022, 10, 29)), "FY23Q1", IF(AND([RR Target Start Date]@row >= DATE(2022, 10, 30), [RR Target Start Date]@row <= DATE(2023, 1, 28)), "FY23Q2", IF(AND([RR Target Start Date]@row >= DATE(2023, 1, 29), [RR Target Start Date]@row <= DATE(2023, 4, 29)), "FY23Q3", IF(AND([RR Target Start Date]@row >= DATE(2023, 4, 30), [RR Target Start Date]@row <= DATE(2023, 7, 29)), "FY23Q4", IF(AND([RR Target Start Date]@row >= DATE(2023, 7, 30), [RR Target Start Date]@row <= DATE(2023, 10, 29)), "FY24Q1", "OTHER")))))))))))))