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]
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]