excel - Formula to give accurate Quarter then Year

Question

I have tried to make a formula using IF but could not successful. I know IF will be use but there are some other functions will be used as well to make.

I am looking for a Excel formula which give a result as available in Col"D". That formula will be dynamic

if C4 is start from 4th month then formula will give result as quarter 2 then 3 and 4, after that year. if C4 is start from 7th month then formula will give result as quarter 3 and 4, after that year. if C4 is start from 9th month then formula will give result as quarter 4, after that year.

That first year will be considered as Quarters and other as Years.

Any help will be greatly appreciated.

Sheet Link.

https://docs.google.com/spreadsheets/d/1jQufqt8WWD8Yoy7MjdOFCJWnUQl4RYX5oTxY_MCTvrA/edit?usp=sharing

Answer 1

Use

=IF(YEAR($D$1)=B4,"Q" & INT((C4-1)/3)+1 & " " & B4,"Y"&B4)