help related to leave balance formula on urgent basis

Copper Contributor

I want to calculate the total leave balance from the date of joining till today(including today's day)

2.5 leaves per month (irrespective of days in the month)

So for Dr Hazem, the correct number should be 22.75

total period: Jan to September = 9 months & 3 days in October

= (9*2.5)+(3*2.5)/30

= 22.75 days leave balance

I know the calculation but doesn't know how to apply it in the Excel

the number 22.95 is based on the 

((Current Date - joining Date)/30)*2.5




7 Replies


Try this.

Add this Funtion it will work
*Note: change the cell format to General

But if I change the current date to April ,30 2023
then it doesn’t show 12.5 while it should be 10

Basically if i put any date above 15 then number goes wrong

If I change the date in column H, the number doesn’t change.
I didn't use column H. I used TODAY(). You can change it in the formula.
no dear..this is the formula i applied..not right
using this the number comes as 22.95 while it should be 22.75