Holiday Entitlement formula calculation

[Jacko1307]

Hi,

I am trying to create a formula which changes with the dates entered for partial year entitlements.

a. Annual Leave = 28 Days
b. Leave Year starts on 01 Jan each Year.
c. If a person commences work on say 01 June 2020 the entitlement would be 1/12th of the annual year entitlement and every month would increase so 01 July it would be 2/12ths and so on for each month worked.
I have two Cell References that I have dates for (E8) start date & (H8) End Date.

I am using NETWORKDAYS between the two date but am having a bit of trouble working out the MONTH number calculation as it is dynamic.
Formula at present in Cell (K8) RESULT = "=NETWORKDAYS(E8,H8)-MONTH(H8-E8)/12"

Any help much appreciated

 

[Dave Patton]

Book1
	D	E	F	G
2
3	Current Yr	01-Jan-20	31-Dec-20
4
5
6
7	Annual	Start		Entitlement
8	28	01-Jun-20	15-Sep-20	7.00
9		01-Jan-19	31-Dec-20	28.00
10		01-Jun-20	31-Jul-20	4.67
1a

Cell Formulas
Range	Formula
G8	G8	=DATEDIF(E8-1,F8,"M")/12*D8
G9:G10	G9	=DATEDIF(MAX($E$3-1,E9-1),F9,"M")/12*$D$8

 

[jasonb75]

Using the table provided by @Dave Patton for testing, here's a single formula that will do full and partial years instead of having different ones for each.

=MIN(DATEDIF(E8-1,F8,"M"),12)/12*$D$8

 

[Jacko1307]

Thank you brilliant works great.

I was trying to over complicate workings.

Best Regards and Happy New Year