Date based accrual formula adding time
Hoping someone can figure out what it going on with this formula! I'm trying to create a formula that will automatically calculate an employees vacation balance based on three variable:
- The employees start date relative to the date the entry is made
- The accrual of .8333 days per month (10 days off per year divided by 12 months)
- The subtraction of any PTO that is accounted for in column E of the spreadsheet
The formula in column G almost works but it is adding time at slightly increasing increments as the formula is applied to future entries. To be more specific: in cell G6 it adds 1, in cell G7 approximately 1.2 and up and up...
Formula currently looks like this:
=IF((YEAR(D6)-YEAR(D5))*12+MONTH(D6)-MONTH(D5)>0,(G5+((YEAR(D6)-YEAR(D5))*12+MONTH(D6)-MONTH(D5)*10/12))-E6,(G5-E6))
Spreadsheet is attached but to map the fields:
D6 = Date of entry
D5 = Date of previous entry
G5 = Previous balance (in days)
E6 = Number of days to be subtracted from employee vacation balance
Totally stumped and any help is appreciated!
Last edited by TheJessle; 09-29-2011 at 02:43 PM. Reason: Problem Solved
How about
=G$5+N(DATEDIF($B$2,D6,"M")*10/12)-SUM($E$5:E6)
in G6 and down
You might be able to simplify the formula using DATEDIF to get the difference in months -try this formula in G6 copied down
=DATEDIF(D5-DAY(D5)+1,D6,"m")*10/12+G5-E6
Audere est facere
Re: Date based accrual formula adding time
PERFECT! Thank you both - that solved the problem wonderfully.
I guess that's what happens when you get too complicated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks