# Thread: Accruing vacation and tracking vacation (Excel 2003)

1. ## Accruing vacation and tracking vacation (Excel 2003)

Good evening...I am attaching a sample of a spreadsheet that I am trying to finish off....I think that most of it is self-explanatory. What I am having trouble with is real-time vacation accrual: vacation accrues at the rate of 2 days/month, at the end of each month. In my sample, "A" has no vacation left over from last year (ie: vacation that he earned in 2006); he earned 24 days in 2007 that he can use in 2008...as 2008 unfolds, he will earn 2 days at end of each month..(ie: at end of January he will be holding 26 vacation days) I want an accurate count of how much vacation he has available to him at any point in the year and so I am looking for a formula in D13:M13 that will 'see' month-end (as it occurs..maybe a TODAY() formula?) and add 2 days at the end of each month to the cells below in A14:M14 (so that it is a 'real-time' picture) which will then total up in row 15.....

2. ## Re: Accruing vacation and tracking vacation (Excel

???..???...Steve-I tried this formula in the sample, in D13, and it says #NAME.....is this a formula that will not work until 2008 actually arrives? I think there is something missing b/c often when I enter a formula, I do so in small case letters (and then if the formula works, everything converts to upper case, and if it doesn't convert, then I know something is missing....in this case, the 'today' didn't convert)....and originally, it was saying that there were too many functions....

3. ## Re: Accruing vacation and tracking vacation (Excel

Sorry, I was testing it with a range named "Today" since "Today" does not change. "Today" in the formula should be "Today()". I will correct my original...

Steve

4. ## Re: Accruing vacation and tracking vacation (Excel 2003)

=IF(YEAR(Today())>=2008,DATEDIF(DATE(2008,1,1),Tod ay()+1,"m"),0)*2

If the date is 2008 or later, there will be zero days. otherwise on the last day of each month 2 more days will be added. If the 2 days are added at the start of the following month you would use:

=IF(YEAR(Today())>=2008,DATEDIF(DATE(2008,1,1),Tod ay(),"m"),0)*2

Steve

5. ## Re: Accruing vacation and tracking vacation (Excel

Thank you for that..it works beautifully!

6. ## Re: Accruing vacation and tracking vacation (Excel

Hi Steve....I jumped the gun a bit on this with my last post....I forgot (didn't notice until I started using it) that, if possible, the formula should recognize that the 2 days only commences upon employment.....for example, if someone were to be hired in Sept, they would only earn 8 days for that year (2 * 4 months)....the existing formula overlooks the 'month of hire'......I re-did my sample spreadsheet and you will see that D and J (who started work in 2007) show the same amount of accrual as everyone else, when in fact, at this point, D should only have 20 days (Feb-Nov) and J should only have 10 (July, Aug, Sept, Oct, Nov....b/c he started after mid-month and so earned nothing for June...only July onward......I am re-attaching the workbook, and changed it to be for 2007 so that the formulae actually work, b/c we are in 2007......I realize now that this may becoming too complex (?) but is there any way to tie all of this into the 'date of hire', such that holidays are only earned starting with commencement (and only if hired before the 15th of the month; if hired after, then accrual begins as of the 1st day of the month after being hired)...?

7. ## Re: Accruing vacation and tracking vacation (Excel

Hi Steve....I re-posted my earlier post with some new info that I hadn't thought of..along with a modified workbook....would you be able to take another look at it? Thank you

8. ## Re: Accruing vacation and tracking vacation (Excel

=IF(YEAR(TODAY())>=2007,DATEDIF(MAX(D10,DATE(2007, 1,1)),TODAY(),"m"),0)*2

Steve

9. ## Re: Accruing vacation and tracking vacation (Excel

Thank you...it seems to work just fine....you guys on this forum (you, Hans, Jerry and others are so helpful, we would be lost without you) Thank you; enjoy the rest of the weekend.

10. ## Re: Accruing vacation and tracking vacation (Excel

Steve....can you explain OFFSET to me....the Help files in Excel make no sense to me...I am attaching a 2nd version of the vacation tracker that you helped me with....I've added a 2nd page (Report) and I am trying to use OFFSET to copy the numbers...I can get the Reference part right, but I can't get the formula to move from column to column (to the right) in order to pick up the numbers for each person......thank you, again

11. ## Re: Accruing vacation and tracking vacation (Excel

If you want to "hardcode" the offset values, you must change the 2 from each one (or calculate it). You could calculate it from the row/column you are in. In D5 enter:
=OFFSET(Vacation!\$D\$11,COLUMN()-4,ROW()-5)

Copy D5 to D5:I9

But you will have to change the formulas based on where you place the data in the sheet. I think basing it on the row/column "headings" is more general. In D5

=OFFSET(Vacation!\$D\$11,MATCH(D\$4,Vacation!\$A\$11:\$A \$16,0)-1,MATCH(\$C5,Vacation!\$D\$9:\$H\$9,0)-1)

Or to eliminate the "-1"s just start at C10:
=OFFSET(Vacation!\$C\$10,MATCH(D\$4,Vacation!\$A\$11:\$A \$16,0),MATCH(\$C5,Vacation!\$D\$9:\$H\$9,0))

I prefer instead of OFFSET I would use an INDEX with 2 MATCHes I think this is more "intuitive". In D5 enter:
=INDEX(Vacation!\$D\$11:\$H\$16,MATCH(D\$4,Vacation!\$A\$ 11:\$A\$16,0),MATCH(\$C5,Vacation!\$D\$9:\$H\$9,0))

Alternately with either INDEX or OFFSET you could point to the "indexvalue" of the "offset" of the rows and columns as (possibly hidden) row and column instead of calculating it from the MATCH.

Steve

12. ## Re: Accruing vacation and tracking vacation (Excel

...thank you for all of that, Steve...it will take a bit to digest it all, but it sure gives me some different ways to do it....thank you!

13. ## Re: Accruing vacation and tracking vacation (Excel

Hello again Steve...I have returned to this post (from 2 weeks ago)....you had helped me with a formula that would automatically show vacation accrual as the months passed during a year; as I read your formula (which is =IF(YEAR(TODAY())>=2007,DATEDIF(MAX(D10,DATE(2007, 1,1)),TODAY(),"m"),0)*2), it is set up so that if someone starts work anytime up to and including the 15th day of a month, they earn 2 days vacation (and if they don't start to work till after the 15th they earn nothing in that month.

Is it possible to adjust it so that if someone starts working anytime from the 16th on, they would earn 1 day (for that partial month) and thereafter would earn 2 days for each complete month worked....in other words, for each 1/2 month, they earn 1 day...?? Thank you...

14. ## Re: Accruing vacation and tracking vacation (Excel

=IF(YEAR(TODAY())>=2007,2*DATEDIF(MAX(D10,DATE(200 7,1,1)),TODAY(),"m")+(DAY(D10)<16)+1,0)

15. ## Re: Accruing vacation and tracking vacation (Excel

Hi Hans.....your code seems to work fine with one exception...I have attached a sample wbook and you'll see that everything seems to calculate properly for some (B,C,D etc) but not for all (see A)...??..??....what I am shooting for is that if you start work anytime up to & including the 15th of a month, it counts as a full month for vacation accrual purposes; if you start anytime from the 16th on, that is a 1/2 month for vacation accrual purposes.....and, conversely, if you quit up to and including the 15, it counts as a 1/2 month for vacation accrual purposes..and if you quit anytime from the 16th-month end, it counts as a full month for vacation accrual purposes...I have tried to explain this in the attachment

Page 1 of 5 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•