You are here:

Excel/Excel Formulas for vacation accrual

Advertisement


Question
I have two issues that are currently stumping me.

(1) I need a formula that flips a date to the next year when remaing vacation hours equal zero.

(2) For sick leave our company pays 4 hours per month based on the hire date. I have two employees that were hired this year. One in February and one in March. I cannot get their 4 hours per month to calculate properly. It is taking them as a whole 12 years of service.

THose are the last two issues i am facing before completing this project to revamp the current spreadsheets. Can you help?

Answer
Bess,

No context, no cell references, no ranges, etc.  So my answers will have to make assumptions and be generic.

I want to return today's date if I sum up A1:L1 and get a positive number (remaining vacation days).  If the sum is zero, return one year from today:

=IF(SUM(A1:L1)=0,DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())),TODAY())


you can get the number of months between two dates with

=DATEDIF(C5,D5,"m")

where C5 is the start date and D5 is the end date.  I used
c5: Mar, 15, 2013
D5:  today()

I get a result of 5.  YOu would multiply this by 4.  

Now I assume you have some type of rules about how to count months for this purpose.  If I make C5  Mar, 31, 2013, then the function returns 4.  (5 full months have not elapsed).  

If you have specific rules, then I could probably suggest some modifications to the dates so you count properly

for example  

Date(year(c5),month(c5),1)   would give me the first of the hire month
Date(year(c5),month(c5)+1,0)  would give me the last day of the hire month (regardless of whether it is 28, 29, 30 or 31).   You could make similar modifications to the "end" date you want to use.  

--
Regards,  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.