Leave Balance Calculations

huntkey

New Member
Joined
Jan 26, 2014
Messages
3
Greetings,


I want a formula to calculate annual leaves for employees. I've been looking through the forums and found something similar > http://www.mrexcel.com/forum/excel-questions/677610-calculation-leave-days.html <


But the formula wouldn't suit what we're working with. I could use some changes but I'm not that versed in Excel ._.


Using Excel 2010


C3: Start date of contract e.g. 23/5/2014
D3: Calculation date e.g. 31/12/2014
F3: Total number of leave days (balance until the end date with | IF E3> X.5 THEN F3=F3+1|) The result in this case for those in 35 will be (0.681+20.417=21.098 = 21 days)


We work with 4 types of leaves per year (35, 45, 60, 30 days per year) regardless of working days/national holidays.


Right now what we're using is the simple calculations (35/12 = 2.916 per month, /30 = 0.0972 per day or 45/12 = 3.75 days per month, 3.75/30 = .125 per day etc)


I tried using the formula on the other topic but it only show leave days per month, if we entered a date in the middle of the month it'll calculate the months balance. We want it to recognize the days from X to 30 inclusive

I'm kind of new to Excel in this regard with formulas and all, any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,994
Members
414,418
Latest member
mightyMagnus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top