formula--if/then
 

I currently use excel prof. 2003 as vacation tracking software for our
startup. Our policy is such that for the 1st year you accrue 15 days, second
yr. 16, 3rd yr. 17, 4th yr. 18, 5th year 19 and max out over your 6th yr at
20 days that you can accrue. The max you can carryover year to year is your
yearly accrual plus an additional 10 days. My accrual is obviously based on
the person's start date and today's date. The problem i am having now is that
we have people that haven't taken vacation so their balances are exceeding
what they can earn in a given year. How do i create a formula so that if the
number accured is higher than what they can earn the max that they can earn
is substitued instead?

formula--if/then
 

Something like this:

=MIN(earned,accrued)

so that if accrued is 15 and earned is 18, this will return 15.

Hope this helps.

Pete

On Jul 23, 5:36 pm, GL109 wrote:
I currently use excel prof. 2003 as vacation tracking software for our
startup. Our policy is such that for the 1st year you accrue 15 days, second
yr. 16, 3rd yr. 17, 4th yr. 18, 5th year 19 and max out over your 6th yr at
20 days that you can accrue. The max you can carryover year to year is your
yearly accrual plus an additional 10 days. My accrual is obviously based on
the person's start date and today's date. The problem i am having now is that
we have people that haven't taken vacation so their balances are exceeding
what they can earn in a given year. How do i create a formula so that if the
number accured is higher than what they can earn the max that they can earn
is substitued instead?

formula--if/then
 

Perhaps ....

=MIN(Accrual, Max allowed)

"GL109" wrote:

I currently use excel prof. 2003 as vacation tracking software for our
startup. Our policy is such that for the 1st year you accrue 15 days, second
yr. 16, 3rd yr. 17, 4th yr. 18, 5th year 19 and max out over your 6th yr at
20 days that you can accrue. The max you can carryover year to year is your
yearly accrual plus an additional 10 days. My accrual is obviously based on
the person's start date and today's date. The problem i am having now is that
we have people that haven't taken vacation so their balances are exceeding
what they can earn in a given year. How do i create a formula so that if the
number accured is higher than what they can earn the max that they can earn
is substitued instead?

formula--if/then
 

Thanks guys! That will work for a short-term solution. The only problem i
have with it is i have to manually adjust everyone's min every year. I was
looking for something that would incorporate the start date so i could use
the same forumula for everyone. But this is definitely a good start! maybe i
could do some kind of formula with if/then and the start date AND min. I'm
going to play around. Thanks again!!

"Pete_UK" wrote:

Something like this:

=MIN(earned,accrued)

so that if accrued is 15 and earned is 18, this will return 15.

Hope this helps.

Pete

On Jul 23, 5:36 pm, GL109 wrote:
I currently use excel prof. 2003 as vacation tracking software for our
startup. Our policy is such that for the 1st year you accrue 15 days, second
yr. 16, 3rd yr. 17, 4th yr. 18, 5th year 19 and max out over your 6th yr at
20 days that you can accrue. The max you can carryover year to year is your
yearly accrual plus an additional 10 days. My accrual is obviously based on
the person's start date and today's date. The problem i am having now is that
we have people that haven't taken vacation so their balances are exceeding
what they can earn in a given year. How do i create a formula so that if the
number accured is higher than what they can earn the max that they can earn
is substitued instead?