Results 1 to 15 of 64

20071215, 03:21 #1
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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 selfexplanatory. What I am having trouble with is realtime 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' monthend (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 'realtime' picture) which will then total up in row 15.....

20071215, 16:15 #2
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Accruing vacation and tracking vacation (Excel
???..???...SteveI 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....

20071215, 17:30 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20071215, 17:31 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Accruing vacation and tracking vacation (Excel 2003)
<P ID="edit" class=small>(Edited by sdckapr on 15Dec07 11:31. Corrected mistake in formula)</P>How about this?
=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

20071215, 17:45 #5
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Accruing vacation and tracking vacation (Excel
Thank you for that..it works beautifully!

20071215, 18:28 #6
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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 redid 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 (FebNov) and J should only have 10 (July, Aug, Sept, Oct, Nov....b/c he started after midmonth and so earned nothing for June...only July onward......I am reattaching 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)...?

20071215, 18:29 #7
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Accruing vacation and tracking vacation (Excel
Hi Steve....I reposted 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

20071216, 00:40 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Accruing vacation and tracking vacation (Excel
How about In D13:
=IF(YEAR(TODAY())>=2007,DATEDIF(MAX(D10,DATE(2007, 1,1)),TODAY(),"m"),0)*2
Steve

20071216, 00:57 #9
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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.

20071216, 02:17 #10
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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

20071216, 05:30 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20071216, 05:33 #12
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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!

20080105, 06:59 #13
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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...

20080105, 12:09 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Accruing vacation and tracking vacation (Excel
How about this?
=IF(YEAR(TODAY())>=2007,2*DATEDIF(MAX(D10,DATE(200 7,1,1)),TODAY(),"m")+(DAY(D10)<16)+1,0)

20080105, 16:44 #15
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
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 16thmonth end, it counts as a full month for vacation accrual purposes...I have tried to explain this in the attachment