Excel formula for date

[periukera]

Hello, can anyone help me with excel formula for the column Month before year start, Duration (months), Month after year end and Months in 21/22 below please :-

FY 21/22	Year Start: 01/08/2021	Year End: 01/08/2022
Project	Project Dates Start	Month before year start	Project Dates End	Duration (months)	Month after year end	Months in 21/22
1	01/05/2021		30/04/2025
2	01/12/2020		30/11/2021
3	01/09/2020		31/08/2023
4	01/01/2021		31/12/2021
5	01/10/2021		30/09/2025

 

[Saba Sabaratnam]

Hi

I have your data in my spreadsheet in this layout. Pease note that I split text and date of year start and year end into two cells.

Enter the following formulas in the given cells and copy them down,

Cell C4 - YEARFRAC(B4,$B$2)*12
Cell E4 -YEARFRAC(B4,D4)*12
Cell F4 -YEARFRAC($C$2,D4)*12
Cell G4 -MIN(12,F4)

Kind regards

Saba

 

[Crystalyzer]

Book10
	A	B	C	D	E	F	G
2	FY 21/22	Year Start: 1/8/2021	Year End: 1/8/2022
3	Project	Project Dates Start	Month before year start	Project Dates End	Duration (months)	Month after year end	Months in 21/22
4	1	5/1/2021	7	30/4/2025	53	33	12
5	2	1/12/2020	8	30/11/2021	12	0	4
6	3	1/9/2020	11	31/8/2023	36	13	12
7	4	1/1/2021	7	31/12/2021	12	0	5
8	5	1/10/2021	0	30/9/2025	49	39	10
Sheet1

Cell Formulas
Range	Formula
E4:E8	E4	=ROUND((D4-B4)/30,0)
F4:F8	F4	=IF(ROUND(($D4-$C$2)/30,0)>0,ROUND(($D4-$C$2)/30,0),0)
G4:G8	G4	=ROUND((MIN(D4,$C$2)-MAX(B4,$B$2))/30,0)
C4:C8	C4	=IF(ROUND(($B$2-$B4)/30,0)>0,ROUND(($B$2-$B4)/30,0),0)