?
Solved

Looking for an Excel formula that will add 1.25 days at the end of every month

Posted on 2012-09-17
7
Medium Priority
?
603 Views
Last Modified: 2012-09-18
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
Comment
Question by:itkadmin
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 1000 total points
ID: 38408170
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
 
LVL 12

Assisted Solution

by:tilsant
tilsant earned 1000 total points
ID: 38408409
"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
 
LVL 12

Expert Comment

by:tilsant
ID: 38408451
file attached..
Accrued-Leaves.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:itkadmin
ID: 38409376
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
 
LVL 39

Expert Comment

by:nutsch
ID: 38409652
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
 
LVL 39

Expert Comment

by:nutsch
ID: 38409679
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
 
LVL 2

Author Closing Comment

by:itkadmin
ID: 38409741
Both solutions work great! Thank you.

I'll contact HR to see which works best for them.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question