Date & Quarters

[raheem]

Hi,

I need a simple formula to get the quarter number from date. In below table, if column A is already date, it should mention the quarter in column B. But if column A is already quarter, it should simply equal in column B. Changing date format is not an option. I tried to create a formula with IF+MONTH+SWITCH etc. but it becomes too complex to understand for other users.

[TABLE="width: 227"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]01.01.2018[/TD]
[TD="align: center"]Q1[/TD]
[/TR]
[TR]
[TD="align: center"]05.02.2018[/TD]
[TD="align: center"]Q1[/TD]
[/TR]
[TR]
[TD="align: center"]10.03.2019[/TD]
[TD="align: center"]Q1[/TD]
[/TR]
[TR]
[TD="align: center"]03.04.2019[/TD]
[TD="align: center"]Q2[/TD]
[/TR]
[TR]
[TD="align: center"]31.05.2018[/TD]
[TD="align: center"]Q2[/TD]
[/TR]
[TR]
[TD="align: center"]Q2[/TD]
[TD="align: center"]Q2[/TD]
[/TR]
[TR]
[TD="align: center"]Q2[/TD]
[TD="align: center"]Q2[/TD]
[/TR]
[TR]
[TD="align: center"]Q3[/TD]
[TD="align: center"]Q3[/TD]
[/TR]
[TR]
[TD="align: center"]Q3[/TD]
[TD="align: center"]Q3[/TD]
[/TR]
[TR]
[TD="align: center"]Q4[/TD]
[TD="align: center"]Q4[/TD]
[/TR]
[TR]
[TD="align: center"]Q4[/TD]
[TD="align: center"]Q4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mr_Lucky]

Awesome that worked perfectly. Many thanks

Do you know if there is a way to convert quarters e.g Q3 2019 to the end date of the quarter 30-Sep 2019.

 

[FormR]

convert quarters e.g Q3 2019 to the end date of the quarter 30-Sep 2019.

Hi, here is one option you can try:

Excel 2013/2016
	A	B
2	Q3 2019	30/09/2019
3	Q1 2019	31/03/2019
4	Q2 2019	30/06/2019
5	Q4 2019	31/12/2019
Sheet1

Cell Formulas
Range	Formula
B2	=DATE(RIGHT(A2,4),3*MID(A2,2,1)+1,0)

 

[Mr_Lucky]

That works perfectly. Many thanks