Page 1 of 5 123 ... LastLast
Results 1 to 15 of 64
  1. #1
    4 Star Lounger
    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 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.....
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 15-Dec-07 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

  5. #5
    4 Star Lounger
    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!

  6. #6
    4 Star Lounger
    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 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)...?
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    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. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    4 Star Lounger
    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.

  10. #10
    4 Star Lounger
    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
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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

  12. #12
    4 Star Lounger
    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!

  13. #13
    4 Star Lounger
    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...

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

  15. #15
    4 Star Lounger
    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 16th-month end, it counts as a full month for vacation accrual purposes...I have tried to explain this in the attachment
    Attached Files Attached Files

Page 1 of 5 123 ... LastLast

Posting Permissions

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