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

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
itkadmin
Asked:
itkadmin
  • 3
  • 2
  • 2
2 Solutions
 
nutschCommented:
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
 
tilsantCommented:
"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
 
tilsantCommented:
file attached..
Accrued-Leaves.xlsx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
itkadminAuthor 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
 
nutschCommented:
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
 
nutschCommented:
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
 
itkadminAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now