I have a spreadsheet with Names in column A, Hire date in column B, 4 year anniversary Date in Column C, 8 year anniversary date in column D, this continues until 24 year in column H. The City has come up with a pay plan, every 4 years of service the employee gets a pay increase. The problem is the city wants to only do pay increases once a year. This means on July 1st they do pay increases, if a employee is anniversary date is July 10th that employee has to wait roughly a year to get the rate increase. To aid them i wanted to create a spreadsheet that did the work for them where all they had to do was open the spreadsheet when payroll is being done and see a list of name that need a pay increase. Unfortunately, I have not been able to come up with the formulas needed to effectively do this. My thought is to obtain a list of names who's anniversary date falls between a range of dates. The range of dates would be the pay period. Example: If John and Steven anniversary date is 07/23/2022 and the pay period is 07/23/2022-08/05/2022 then John and Steven's name would be put into a cell. Column B through H have a varying amount of dates some of which have already past. The formula i came up with is =LOOKUP(2,1/($K$2<=C2:H63)/($L$2>=C2:H63),$A$2:$A$63) which kind of works but i only get one name not multiple and it doesn't seem to search the entire range of dates. It also isn't really automatic and will need to be updated for the following years. Any thoughts or suggestions would be greatly appreciated.
- Page :
There are no replies made for this post yet.