Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|