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

 

[strail1972]

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

Hello @jasonb75 - I am trying to calculate pro-rata leave for employees starting or leaving part way through a year. I have used your suggested formula however for an annual leave entitlement of 25 days and someone leaving say 20/12/2024 (year starts 01/01, year ends 31/12) the formula is returning a value of 22.92, however, using online calculators such the value returns at 24.5 or 25 depending if rounding up to nearest 0.5 or whole number.

Do you have any suggestions as to why this would be happening as we would like the value in using excel to match if that is possible.

Thank you

 

[Dave Patton]

Please show how the value that you want is calculated.
What are the rules applicable for the calculation?
Is the calculation by months completed or days completed?

 

[jasonb75]

Hello @jasonb75 - I am trying to calculate pro-rata leave for employees starting or leaving part way through a year. I have used your suggested formula however for an annual leave entitlement of 25 days and someone leaving say 20/12/2024 (year starts 01/01, year ends 31/12) the formula is returning a value of 22.92, however, using online calculators such the value returns at 24.5 or 25 depending if rounding up to nearest 0.5 or whole number.

Do you have any suggestions as to why this would be happening as we would like the value in using excel to match if that is possible.

Thank you

The formula was based on whole months only as per the requirements of the original question. This would be 11 months based on your example dates, which works out at 22.92 days when calculated pro-rata.

The result that you are expecting appears to be taking partial months into consideration as well, this formula should do what you want it to.

Book1
	A	B
2	Year start	01/01/2024
3	Year end	31/12/2024
4
5	Emp start	01/01/2024
6	Emp end	20/12/2024
7
8	Entitlement	25
9
10	Result	24.5
Sheet1

Cell Formulas
Range	Formula
B10	B10	=CEILING((B6-B5)/(B3-B2)*B8,0.5)