Excel - Calculate Qtrs & dates

[excel_helps!]

Hi There,

I have a specific work that I am trying to resolve but somehow am unable to solve the same.

so basically in col A and Col B I have project start date and project end date. i have minisheet attached below.

what help i need from the group is to update 4 columns using Col A and Col B

in Col C - if the project start date is 1st day of Qtr then in which Qtr i am in ? (in number for ex: 2, 3, 4)
in col D - how many Qtrs will the project run for?
in col E - if i am in 3rd Qtr what is the start date of the Qtr
in col F - if i am in 3rd Qtr what is the End date of the Qtr

If you guys can help me with this i will be out of this Jam and would really appreciate the help. i am Stuck and my work is not progressing.

Thank you so much in advance.

regards,
M

sample.xlsb
	A	B	C	D	E	F
1			From project start date
2	Project Start Date	ProjectEndDate	Qtr No.	Total Qtrs between start and end date	Start date of current Qtr	End date of current Qtr
3	7/1/2021	6/30/2023
4	7/1/2021	5/31/2022
5	1/1/2021	2/28/2022
6	10/1/2020	11/30/2022
7	10/1/2021	9/30/2023
8	10/1/2020	12/31/2022
9	4/15/2021	5/31/2022
10	10/1/2020	9/30/2022
11	7/1/2020	12/31/2021
12	12/15/2020	12/31/2023
Sheet1

 

[Alex Blakenburg]

I am sure someone can come up with something better but try this for starters.
PS: I am in Australia so the date format is dd/mm/yyyy

20211224 Work out Quarters.xlsx
	A	B	C	D	E	F
1			From project start date		Current Date	25/12/2021
2	Project Start Date	ProjectEndDate	Qtr No.	Total Qtrs between start and end date	Start date of current Qtr	End date of current Qtr
3	1/07/2021	30/06/2023	3	7	1/10/2021	31/12/2021
4	1/07/2021	31/05/2022	3	3	1/10/2021	31/12/2021
5	1/01/2021	28/02/2022	1	4	1/10/2021	31/12/2021
6	1/10/2020	30/11/2022	4	8	1/10/2021	31/12/2021
7	1/10/2021	30/09/2023	4	7	1/10/2021	31/12/2021
8	1/10/2020	31/12/2022	4	8	1/10/2021	31/12/2021
9	15/04/2021	31/05/2022	2	4	1/10/2021	31/12/2021
10	1/10/2020	30/09/2022	4	7	1/10/2021	31/12/2021
11	1/07/2020	31/12/2021	3	5	1/10/2021	31/12/2021
12	15/12/2020	31/12/2023	4	12	1/10/2021	31/12/2021
Sheet1

Cell Formulas
Range	Formula
F1	F1	=TODAY()
C3:C12	C3	=ROUNDUP(MONTH($A3)/3,0)
D3:D12	D3	=4*(YEAR(B3)-YEAR(A3))+ROUNDUP(MONTH($B3)/3,0)-$C3
E3	E3	=DATE(YEAR($F$1),CHOOSE(ROUNDUP(MONTH($F$1)/3,0),1,4,7,10),1)
F3:F12	F3	=EOMONTH(E3,2)
E4:E12	E4	=DATE(YEAR(TODAY()),CHOOSE(ROUNDUP(MONTH(TODAY())/3,0),1,4,7,10),1)