• Status: Solved
• Priority: Medium
• Security: Public
• Views: 677

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
0
• 3
• 2
• 2
2 Solutions

Commented:
Assuming the hire date is in cell A3, you can try this formula:

=SUM(IF(DAY(1+ROW(OFFSET(A1,A3-1,1,TODAY()-A3,1)))=1,1.25,0))

entered as an array formula, with Ctrl+Shift+Enter (once you enter it like that, curly brackets will appear around the formula)

Thomas
0

Commented:
"At the end of every month", if you mean 30 or 31 days from the date of joining, then you may use the below formula:
=DATEDIF(B2,TODAY(),"m")*1.25
provided the joining date is mentioned in cell B2.

Tils.
0

Commented:
file attached..
Accrued-Leaves.xlsx
0

Author Commented:
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
0

Commented:
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
0

Commented:
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
0

Author Commented:
Both solutions work great! Thank you.

I'll contact HR to see which works best for them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.