Formula for unpaid leave adjustment

[MamoCTSA]

Hi everyone

I am a beginner in excel. Here are my two questions:

1. How do I calculate the exact number of days in a month in excel? Days gives me the days between the first & last day of the month, so I manually adjusted for the short 1 day.

2. I need a formula that calculates the leave adjustment required when employees take unpaid leave.

i.e if Steven took unpaid leave in March, the formula needs to recognise that March has 31 days of which Steven took 8 (working days) as unpaid leave and therefore we need to adjust his March accrual of 1.69 with 8 / 31 * 1.69

How do I do this?
I need the formula to be able to span over months (e.g if unpaid leave is in March & April, the formula needs to recognise and calculate accordingly based on each months’ days & accrual.

 

[MamoCTSA]

File uploaded

 

[Joe4]

Welcome to the Board!

Note that you cannot upload files to this site. However, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

To answer your first question, if you have a valid date in cell A1, you can get the number of days in that month with this formula:

=DAY(EOMONTH(A1,0))