Solved Need help creating a formula for vacation leave

October 15, 2012 at 09:41:47
Specs: Windows Vista
Assume that B6 is the status "FT"
Assume that D6 is Years employed

- 15 days for full-time employees who have worked 4 or more years
- 10 days for full-time employees who have worked 2 or more years but less than 4 years
- 5 days for full-time employees who have worked more than 1 year but less than 2 years
- 0 days for everyone else

Thanks for the help


See More: Need help creating a formula for vacation leave

Report •


#1
October 15, 2012 at 10:16:04
Here is the easiest formula:

=IF(D6>=4,15,IF(D6>=2,10,IF(D6>=1,5,0)))

This works on the principle of descending orders of most strict criterion to least strict.

Think of it this way, It checks to see if the person has been employed for 4 or more years first, if so, it returns "15" for 15 days. If not, it checks to see if they have been employed 2 years or more and returns 10 if they have. We do not need to worry about the upper limit here as anything 4 or more years would have been satisfied by the first "IF" statement. This proceeds on with the 1 year or more and finally if no criterion are met, it returns 0.

Note that I did not use absolute cell referencing as I assume that you will be filling the formula down multiple rows for each employee.

Please let me know if this makes sense or if you need more assistance. Thanks!

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •

#2
October 15, 2012 at 10:43:38
✔ Best Answer
You said: Assume that B6 is the status "FT"

Should we assume that that is important?

SongCloud's suggested formula does not reference B6, so it wouldn't matter if B6 contained FT or not.

Is that an issue?

If you need to check for FT, then perhaps something like this is what you are looking for.

=If(B6="FT",IF(D6>=4,15,IF(D6>=2,10,IF(D6>=1,5,0))),"No Vacation")

This will return No Vacation if B6 is not FT.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 15, 2012 at 11:01:20
DerbyDad03 - Thanks for that! I completely forgot about the FT/not FT possibility. +1!

However, one of your parenthesis is out of place. The correct formula should be:

=If(B6="FT",IF(D6>=4,15,IF(D6>=2,10,IF(D6>=1,5,0))),"No Vacation")

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •

Related Solutions

#4
October 15, 2012 at 11:23:04
Thanks that formula works, but I forgot to tell you in the status There is PT,CN and EX that need to be factored in. Can you help?

Report •

#5
October 15, 2012 at 12:52:02
Sure. What do you want displayed if the data in B6 is PT, CN, or EX?

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •

#6
October 15, 2012 at 13:04:31
SongCloud,

Parenthesis out of place? Where? I don't see any parenthesis out of place.

No, I don't know how to use the editing feature of this website. No, I didn't edit it to fix the parenthesis. That's my story and I'm sticking to it. ;-)

(Nice catch...I should have tested it but I was in a hurry.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
October 15, 2012 at 13:36:30
LOL DerbyDad.

I've been guilty of that more than a few times.

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::


Report •


Ask Question