Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Thanked 0 Times in 0 Posts

    Keep Date dependant sum from changing (Excel 2000)

    Dear all,

    I was very interested in the posting “Accruing vacation and tracking vacation” as I made a similar file. As this particular thread is closed I won’t go into it. I however was wondering if someone out there could help with a problem I try to resolve. In my vacation tracker I have listed all the starting dates of my staff. They all get 20 days annual leave per year. The prorate leave for each employee is worked out by dividing 20/365 * the number of days that have elapsed since the starting date and the current date. The number of days leave taken is subtracted from the total leave available. The problem occurs at when the starting date anniversary arrives. The pro rata leave on the first day is zero, the day before it was 20. What formula can I use to keep the 20 days for example (if no leave was taken that year) and add it to the Total available leave? (Remaining leave plus pro rata leave.) Not sure if I explained it in an understandable fashion.

    Thank you all


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Keep Date dependant sum from changing (Excel 2000)

    I think you'll have to place the data for each new year in separate cells.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Thanked 1 Time in 1 Post

    Re: Keep Date dependant sum from changing (Excel 2

    Hi aussiehans....can you explain it in a bit more detail...if it is the same situation that I had, I could send you the wbook that Steve (and Hans helped me develop...they were (are!!) extraordinarily helpful and I woudl be be happy to share with you

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Keep Date dependant sum from changing (Excel 2000)

    You would calculate the total vac since some start period and then subtract the number of days already taken. The vacation tracking would involve keeping track of multiple years or read that data from other sheets or just transfer the data at the end of the year.

    If you need more detail you need to attach your setup and elaborate on your design.


Posting Permissions

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