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.

A	B
01.01.2018	Q1
05.02.2018	Q1
10.03.2019	Q1
03.04.2019	Q2
31.05.2018	Q2
Q2	Q2
Q2	Q2
Q3	Q3
Q3	Q3
Q4	Q4
Q4	Q4

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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