Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula to test several conditions
Good evening Excel Gurus
In a situation where I want to determine the number of vacation days an employees would have... In A1 I have the hire date In B1 I want to determine the number of days of vacation the person may have using the following conditions: - From 0 to 6 months -- 0 Days - From 6 months to less than 1 -- 5 days - From 1 to Less than 5 -- 10 days - From 5 to less than 12 -- 5 days - From 12 to 20 -- 20 days This situation is a challenge because in the cell B1 - I need to determine the number of year/months/days based on today's date compared to the HIRE DATE in A1 - I need to have several "if" condition tested at the same time Can someone help? Thanks so much |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula to test several conditions
Create a table like so
............... I..............J............K 1.............20...........20............0 2.............15...........12............0 3.............10.............5............0 4................5............1............0 5...............0.............0............6 then in H1 add and copy down to H5. In B1, use =VLOOKUP(A1,$H$1:$I$5,2) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MIchel Khennafi" wrote in message ... Good evening Excel Gurus In a situation where I want to determine the number of vacation days an employees would have... In A1 I have the hire date In B1 I want to determine the number of days of vacation the person may have using the following conditions: - From 0 to 6 months -- 0 Days - From 6 months to less than 1 -- 5 days - From 1 to Less than 5 -- 10 days - From 5 to less than 12 -- 5 days - From 12 to 20 -- 20 days This situation is a challenge because in the cell B1 - I need to determine the number of year/months/days based on today's date compared to the HIRE DATE in A1 - I need to have several "if" condition tested at the same time Can someone help? Thanks so much |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula to test several conditions
Michel --
Here's one approach: =IF(TODAY()-A1<182,0,IF(TODAY()-A1<365,5,IF(TODAY()-A1<1825,10))) Basically, you create a set of "nested" if-statements in which you put the next if-condition into the false part of the previous if statement. I didn't do the last two parts, mainly out of laziness but also so you could practice it out on your own. HTH "MIchel Khennafi" wrote: Good evening Excel Gurus In a situation where I want to determine the number of vacation days an employees would have... In A1 I have the hire date In B1 I want to determine the number of days of vacation the person may have using the following conditions: - From 0 to 6 months -- 0 Days - From 6 months to less than 1 -- 5 days - From 1 to Less than 5 -- 10 days - From 5 to less than 12 -- 5 days - From 12 to 20 -- 20 days This situation is a challenge because in the cell B1 - I need to determine the number of year/months/days based on today's date compared to the HIRE DATE in A1 - I need to have several "if" condition tested at the same time Can someone help? Thanks so much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cells with a formula (7 conditions). | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions |