Confusing Vacation Math Problem

[jlclyde]

This is a weird one and has me stumped. We are starting a new
vacation plan and you accrue time each week starting from our
effective date or start date, which ever is later. There is a max
value that a person can accrue. So if a person gets to this limit
before they use their time, it should not add in the extra time. I am
fine with it up to this point. When I fill in the hours used, it
takes the time out, then adds in the time that woudl have gone in had
they not been maxed out. It seems to be a loop. Can you help me
think of a way around this? If you want my current formulas, let me
know.

Thanks,
Jay

 

[Pete_UK]

Yes, show your existing formulae, together with a description of the
data layout and the problem yo are having at the moment etc.

Pete

 

[Bernie Deitrick]

Jay,

It is likely that you will need two columns of formulas - a better description of your layout would
help.

HTH,
Bernie
MS Excel MVP

 

[jlclyde]

Here is whart I have done since I posted. I added helper columns for
each week of employment. If the date that we roll out the plan is >
week starting = 0 if it is not and today is > then It looks up a Table
that I have called Rates to lookup the years of service based on teh
column lkup Yr, and return the coresponding weekly Ernd Hrs. There is
another sheet that keeps track of vacations by dates and times used.
the vac Used column pulls in the information if it is between the date
that is on the same row and the date in the next row down. Lost Hrs
is if the sum of all Errnd Hrs - Vac Used to date. If it is > Max
then Ernd Hrs if not then 0. So to get my time for a persons current
PTO it is Ernd - Vac Used -Lost Hrs.

If you coudl help me simplify this I woudl greatly appreciate it.
Since it accrues weekly and accrual rates change year to year. The
rates start at the Sunday of the first week that will be in the next
year. The effective date is the 6-2-08. To check formulas I changed
effective date to 6-1-07.
A B
C D E F
Week Starting Ernd Hrs Lkup Yr Max Vac Used Lost
Hrs
Sunday, May 20, 2007 0 12 248 0 0
Sunday, May 27, 2007 0 12 248 0 0
Sunday, June 03, 2007 3.230769 12 248 0 0
Sunday, June 10, 2007 3.230769 12 248 0 0
Sunday, June 17, 2007 3.230769 12 248 0 0
Sunday, June 24, 2007 3.230769 12 248 0 0
Sunday, July 01, 2007 3.230769 12 248 0 0

Formula in Ernd Hrs column (Rates is a table to find Ernd Vlue
weekly) FT is a cell to let me know if they are Full time or Part
time. If they are one or the other it looks up that column instead to
return ernd hrs)
=IF(A609>Tdy,
0,IF(Eff>A608,0,IF(AND(A609<Tdy,A609>Eff),VLOOKUP(C608,Rates,IF(FT="PT",
6,3)),0)))

Formula in Lkup Yr (S1:S21 is another look up for years of
employment)
=IF(ISERROR(LOOKUP(A610,Sheet1!S$1:S$21,Sheet1!Q$1:Q$21))=TRUE,
20,LOOKUP(A610,Sheet1!S$1:S$21,Sheet1!Q$1:Q$21))

Formula in Max Column
=VLOOKUP(C609,Rates,IF(FT="PT",7,4))

Formula in Vac Used
=SUMPRODUCT((Sheet1!A$12:A$1200>='Data Sheet'!A609)*(Sheet1!A$12:A
$1200<='Data Sheet'!A610)*(Sheet1!C$12:C$1200))

Formula in Lost Hrs Column
=IF(SUM(B$2:B608)-SUM(E$2:E609)+B609>D609,B609,0)

This is the final formula to get time left for a person. (These are
named ranges that relate to the above example)
=ErndHrs-Vac-LstHrs

Please let me know what your thoughts are and how coudl have I made
this easier on myself?

Thank you for your time and effort,
Jay