Extracting Fiscal Year / Financial Year based on the Quarters using the "IF" function with nested "OR" Function.

[Asadmir27]

[TABLE="width: 698"]
<tbody>[TR]
[TD]Date[/TD]
[TD] Quarter[/TD]
[TD] FY[/TD]
[TD][/TD]
[TD]Intended Results
in column "C"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15-07-2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-09-2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-11-2018[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-03-2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-05-2019[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-12-2018[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1905[/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Detail:[/TD]
[TD="colspan: 9"]Suppose our financial year starts from 1st July every year, so, july, aug, sept is 1st Quarter
and so on. Our financial year in this case would be the year of the First Two Quarters.
What would be the year in our first two quarters, it would be considered as our FY.
In above scenario, my first two quarters fall in 2018, while 3rd and 4th quarters fall in 2019.
but i want my financial year to be the year from first and 2nd quarter.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Problem:[/TD]
[TD="colspan: 9"]As i use the above formula to bring 2018 instead of 2019, the result shows 1905. how do I arrive on the correct results i.e. 2019.

This is the formula I used in Column C:
=IF(OR(B2=3,B2=4),YEAR(A2)-1,YEAR(A2))
In column A, I have dates stored, In column B I have extracted Quarters using choose function, while in column C, I have to extract F.Y based on the above formula.

Any help would be highly appreciated...

Thanks & Regards

Asad[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 

[JustynaMK]

Hi Asad,

Can you check your column formatting in column A (the one with dates) or even share a sample of your file?
Your formula (column C - FY) properly returns Fiscal Year date (2018) for all rows on my side. I formatted Column A as Date.

Hope it helps.