Calculate leaves

[uswyne]

Casual Leave balance Calculation

Period Start 01-Jun-2017
Period End 31-Jul-2018

Total Casual Leave 10 for 12 month (.83/month) but it should be calculated from date of joining.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Date of Joining[/TD]
[TD]Opening Leave[/TD]
[TD]Availed Leave[/TD]
[TD]Balance Leave[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]28-July-2017[/TD]
[TD](9)auto calculate[/TD]
[TD](1)manual entry[/TD]
[TD](8)auto calculate[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]01-Oct-2017[/TD]
[TD](7)auto calculate[/TD]
[TD](2)manual entry[/TD]
[TD](5) auto calculate[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]28-July-2017[/TD]
[TD](8) auto calcualte[/TD]
[TD][/TD]
[TD](8) auto calculate[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]01-Oct-2017[/TD]
[TD](5) auto calculate[/TD]
[TD][/TD]
[TD](5) auto calculate[/TD]
[/TR]
</tbody>[/TABLE]

waiting for solution.

Thanks

 

[etaf]

This will calculate complete months

=IF(DAY(TODAY())>=DAY(B2),0,-1)+(YEAR(TODAY())-YEAR(B2)) *12+MONTH(TODAY())-MONTH(B2)

see here
https://support.microsoft.com/en-gb...e-number-of-months-between-two-dates-in-excel

Now you can multiply by 0.83

=(IF(DAY(TODAY())>=DAY(B2),0,-1)+(YEAR(TODAY())-YEAR(B2)) *12+MONTH(TODAY())-MONTH(B2))*0.83