Formula to workout Quarter open date and end date

[Leicester City Fox]

I am looking for a formula where Excel will work out which quarter the start and end fall from the dates given
Q1 Apr – Jun
Q2 July – Sept
Q3 Oct – Dec
Q4 Jan-Mar

Colum A Start Date Colum B End Date Colum C Ans Start Quarter Colum D Ans End Quarter
01/04/2018 12/01/2019 18/19 Q1 18/19Q4

22/05/2018 Blank 18/19 Q1 Open 19/20 Q3 (current)

Anybody has any ideas of the easiest way to get the results in Colum C and Colum D from the dates given in Colum A and B also sometimes there no end date (Colum be blank meaning it’s still current(open)?

Many Thanks

Leicester Fox

 

[jmacleary]

Hi there. For this formula I have assumed your 2 dates are in row 11. Place this formula in C11 and copy across and down.

=IF(ISBLANK(A11),"Open "&MOD(YEAR(EDATE(NOW(),-3)),100)&"/"&MOD(YEAR(EDATE(NOW(),-3))+1,100)&" Q"&INDEX({4,4,4,1,1,1,2,2,2,3,3,3},1,MONTH(NOW()))&" (current)",MOD(YEAR(EDATE(A11,-3)),100)&"/"&MOD(YEAR(EDATE(A11,-3))+1,100)&" Q"&INDEX({4,4,4,1,1,1,2,2,2,3,3,3},1,MONTH(A11)))

 

[Leicester City Fox]

Hi there. For this formula I have assumed your 2 dates are in row 11. Place this formula in C11 and copy across and down.

=IF(ISBLANK(A11),"Open "&MOD(YEAR(EDATE(NOW(),-3)),100)&"/"&MOD(YEAR(EDATE(NOW(),-3))+1,100)&" Q"&INDEX({4,4,4,1,1,1,2,2,2,3,3,3},1,MONTH(NOW()))&" (current)",MOD(YEAR(EDATE(A11,-3)),100)&"/"&MOD(YEAR(EDATE(A11,-3))+1,100)&" Q"&INDEX({4,4,4,1,1,1,2,2,2,3,3,3},1,MONTH(A11)))

Hi Jmacleary

I just want thank you..... your an absolute star...……...That formula worked a treat

Thank you Very Much

The Leicester Fox

 

[jmacleary]

You are very welcome. I guess by your name you follow the foxes? I'm a Villa fan and was gutted when Albrighton went to you but so pleased when you won the league. Maybe again this year?

 

[Leicester City Fox]

Hi Jmacleary

Afternoon

Yeah, I am a Fox’s Fan, I also admire Mark Albrighton he is a humble lad when I met him, he very down to earth …. Can’t believe the Villa let him go for free. I think Liverpool have a good lead but they may chock like spurs lol.

You got hold on to Jack… but I hope you guys stay up

The Leicester Fox