Hello, I have a spreadsheet I use to track Transportation employees who are due raises and additional PTO time. Among several columns, it lists their name, some other info not relevant to here, and their full-time date. The full-time date is normally what drives when Drivers earn additional time off as well as increases in pay. Some of the drivers due to lack of work, had to step down from full-time status to part-time status. When this happens, it can cause their full-time date to be pushed forward if they ever get full time again.
Example: Someone was hired as full-time on 9/11/16. By that date, they would get an increase in pay and time off on 9/11/17. However, that person did lose their full-time bid in 5/6/18 and had to go part-time for 98 days until they had gotten full-time again. So those days as part-time actually then pushed their full-time date to 12/18/16. This new full-time date however isn't in the same column of the sheet as everyone else who didn't lose their full-time bids. Their original full-time date is kept in that initial column for tracking when their very first full-time date was.
What I am wondering is if there is some kind of formula I can use that can encompass changes like that. In subsequent columns, that is where I have date formulas for raises and time accruals that are driven by that full-time date. The first column which is column O, a rate of pay column I just have as =E4 (E4 is where all the full-time dates are) Further along there are columns for accruing the time off, additional raises etc. The problem is though that the person whose situation I mentioned above, has his "New" full-time date listed in column M. The columns between E and M are other dates like when they lost the bid, how many days they were part-time, and when they got new full-time bids. For him, column M is what drives his information in column O and beyond, not column E like everyone else. So like I said, I am wondering if there is some kinda of formula I can use that if it sees a new full-time date somewhere other than column E, it will go by that date instead, but if not, it would go by column E. Rather than me having to manually go through each row of column O and say this one Equals E, this one equals M, so on and so forth. Any thoughts? Let me know if more info is needed.
Example: Someone was hired as full-time on 9/11/16. By that date, they would get an increase in pay and time off on 9/11/17. However, that person did lose their full-time bid in 5/6/18 and had to go part-time for 98 days until they had gotten full-time again. So those days as part-time actually then pushed their full-time date to 12/18/16. This new full-time date however isn't in the same column of the sheet as everyone else who didn't lose their full-time bids. Their original full-time date is kept in that initial column for tracking when their very first full-time date was.
What I am wondering is if there is some kind of formula I can use that can encompass changes like that. In subsequent columns, that is where I have date formulas for raises and time accruals that are driven by that full-time date. The first column which is column O, a rate of pay column I just have as =E4 (E4 is where all the full-time dates are) Further along there are columns for accruing the time off, additional raises etc. The problem is though that the person whose situation I mentioned above, has his "New" full-time date listed in column M. The columns between E and M are other dates like when they lost the bid, how many days they were part-time, and when they got new full-time bids. For him, column M is what drives his information in column O and beyond, not column E like everyone else. So like I said, I am wondering if there is some kinda of formula I can use that if it sees a new full-time date somewhere other than column E, it will go by that date instead, but if not, it would go by column E. Rather than me having to manually go through each row of column O and say this one Equals E, this one equals M, so on and so forth. Any thoughts? Let me know if more info is needed.
