Looking for an Excel formula that will add 1.25 days at the end of every month
I am working on a spreadsheet that calculates employees' accrued leave.
At the end of every month, an employee earns 1.25 days of annual leave. How would I write a formula that does this?
I tried 15/365. 15 is the number of days earned in a year (1.25 x 12months), then dividing by 365 would give me the leave the employee earned to that date. Human Resources says this is incorrect, they don't want the 1.25 days added to the earned leave until the employee has finished the month.
Some staff have been here for several years. I need something that says "for every end of the month a staff member has earned since they began working, add 1.5 days.
I haven't a clue what such a formula would look like. Any ideas?
Thank you
I am using Excel 2010, Windows version
At the end of every month, an employee earns 1.25 days of annual leave. How would I write a formula that does this?
I tried 15/365. 15 is the number of days earned in a year (1.25 x 12months), then dividing by 365 would give me the leave the employee earned to that date. Human Resources says this is incorrect, they don't want the 1.25 days added to the earned leave until the employee has finished the month.
Some staff have been here for several years. I need something that says "for every end of the month a staff member has earned since they began working, add 1.5 days.
I haven't a clue what such a formula would look like. Any ideas?
Thank you
I am using Excel 2010, Windows version
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nutsch, What is the reference to cell A1? Both your solution and tilsant's work, I'm trying to sort out what the difference is.
Thanks to both of you for the replies
Thanks to both of you for the replies
Actually, it should be $a$1 so it copies properly. Basically, it converts your dates into row numbers, so it can analyze all the dates between.
My formula is literally counting the number of dates between hire date and today were the day after is the first of the month.
Thomas
My formula is literally counting the number of dates between hire date and today were the day after is the first of the month.
Thomas
Tils's formula will not work very precisely. As it only count the number of days. So if you were hired at the end of the month, it will not give you credit for that month I you compare with a today that's early in the month.
Try it with 8/30
Thomas
Try it with 8/30
Thomas
ASKER
Both solutions work great! Thank you.
I'll contact HR to see which works best for them.
I'll contact HR to see which works best for them.
Accrued-Leaves.xlsx