Excel/Excel Formulas for vacation accrual
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?
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:
you can get the number of months between two dates with
where C5 is the start date and D5 is the end date. I used
c5: Mar, 15, 2013
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
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.
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