Hi,
I have a table that has 2 columns (Date range and Percent). The date format is MM/DD/YY.
I like to write an IF formula that takes Employee Hire Dates to calculate a value using their Base Salary and multiply by the Percent in the 2nd column. I like to do this for about 500 employees where I can use one same formula to calculate for all employees. For example, if an employee is hired on 6/1/19 and has a Base Salary of $100,000, the formula will calculate: 100,000 x 25%.
Date Percent
1/1/21 - 12/31/21 0%
1/1/19 - 12/31/20 25%
1/1/17 - 12/31/18 50%
1/1/15 - 12/31/16 75%
< 12/31/14 100%
I know I can write something inside the IF formula that looks something like this: IF(AND(A2>=DATEVALUE(“1/1/19”),A2<=DATEVALUE(“12/31/20″)), ..................
but I am not sure how to write the entire full formula to capture all the 5 conditions in the table.
I would appreciate any help you can provide.
Thank you
Bookmarks