Formula to group dates

[aryanaveen]

Hi All

Can you please help me with below scenario

I have dates in (dd/mm/yyyy format) columnA and I need to write a formula in Column B to group the dates in to quarters.

Example if the date is less than or equal to dec 31 2021 then in column B it should appear as "Pre Dec 2021"
If the date falls between 1 Jan 2022 - 31 Mar 2022 then in column B it should appear as "Q1 2022", if dates falls in between 1 Apr 2022 - 30 June 2022 then it should appear as "Q2 2022"

Thanks

 

[FormR]

Hi, here's one option you can try:

Book1
	A	B
1	31/12/2021	Pre Dec 2021
2	04/01/2022	Q1 2022
3	25/02/2022	Q1 2022
4	01/04/2022	Q2 2022
5	30/06/2022	Q2 2022
6	01/07/2022	Q3 2022
7	30/09/2022	Q3 2022
8	01/10/2022	Q4 2022
9	31/12/2022	Q4 2022
10	01/01/2023	Q1 2023
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1<DATE(2022,1,1),"Pre Dec 2021","Q"&ROUNDUP(MONTH(A1)/3,0)&" "& YEAR(A1))

 

[aryanaveen]

Hi, here's one option you can try:

Book1
	A	B
1	31/12/2021	Pre Dec 2021
2	04/01/2022	Q1 2022
3	25/02/2022	Q1 2022
4	01/04/2022	Q2 2022
5	30/06/2022	Q2 2022
6	01/07/2022	Q3 2022
7	30/09/2022	Q3 2022
8	01/10/2022	Q4 2022
9	31/12/2022	Q4 2022
10	01/01/2023	Q1 2023
Sheet1

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1<DATE(2022,1,1),"Pre Dec 2021","Q"&ROUNDUP(MONTH(A1)/3,0)&" "& YEAR(A1))

Thank you FormR, this worked