Hello, I have a spreadsheet that I use to track position codes for employees. Currently, there are only 3 position codes: 10305275 which is the code for any 10 hour shift whether daylight or nite, 10305276 which is for any 8 hour shift, whether daylight or nite, and 10305318 which is for any Janitor position which are always 8 hour shifts. My column headers are as follows with a sample shift and I will Bold the relevant headers:

As you see, the tab with these headers contains all the information about a specific shift. The info I am concerned with is accruals(8 or 10 hour position), Job (Janitor and Freezer Janitor are abbreviated Jan and FR Jan), and the Position change code.

In the Position Change code is where currently I have a formula that assigns a code number based on either their accruals or their Job. a 10 hour position will always be the 10305275 number. The 8 hour position gets tricky because as above, it is always 10305276 UNLESS they hold a Janitor position in which case that is the 10305318 number. So the formula to recognize this is as follows:

They are now introducing a new code that they want assigned to all nite shift non-janitor employees: 20209586. The way we tell who is nite shift is by the DEPT. column where it has C61C. Those codes are what designate nite shift or day shifts. Nite shift has 3 different dept. codes: C61Q, C65S, and C67G. If it helps any, a nite shift is always an 8 hour shift, not 10 ( I don't know if that might be relevant for what's needed) Again whether they are day or nite, Janitors will always have that 10305318 code.

How can I incorporate this new code criteria into the formula so that I still have my automation in the sheet?

Sorry I was just informed that there IS a possibility they will have a 10 hour nite shift some time down the road. There aren't any yet but if possible, I would like that have that incorporated in the formula just in case. That code is 20209600.

BID # | DAYS OFF | HOURS | DEPT | PAY RULE | RATE | SORT CODE | ACCRUALS | CLOCK | BID # | JOB | CODE | VAC | POSITION CHANGE CODE |

100 | S/S | 9A-5P - (30 GROC) | C61C | DAY WHSE | 25.17 | C61C | 8 | 8 OR 9 | 4000 | LIFT | G | B | 10305276 |

As you see, the tab with these headers contains all the information about a specific shift. The info I am concerned with is accruals(8 or 10 hour position), Job (Janitor and Freezer Janitor are abbreviated Jan and FR Jan), and the Position change code.

In the Position Change code is where currently I have a formula that assigns a code number based on either their accruals or their Job. a 10 hour position will always be the 10305275 number. The 8 hour position gets tricky because as above, it is always 10305276 UNLESS they hold a Janitor position in which case that is the 10305318 number. So the formula to recognize this is as follows:

Excel Formula:

`=IF(COUNT(SEARCH("*JAN",K4))>0,10305318,IF(H4=10,10305275,10305276))`

They are now introducing a new code that they want assigned to all nite shift non-janitor employees: 20209586. The way we tell who is nite shift is by the DEPT. column where it has C61C. Those codes are what designate nite shift or day shifts. Nite shift has 3 different dept. codes: C61Q, C65S, and C67G. If it helps any, a nite shift is always an 8 hour shift, not 10 ( I don't know if that might be relevant for what's needed) Again whether they are day or nite, Janitors will always have that 10305318 code.

How can I incorporate this new code criteria into the formula so that I still have my automation in the sheet?

Sorry I was just informed that there IS a possibility they will have a 10 hour nite shift some time down the road. There aren't any yet but if possible, I would like that have that incorporated in the formula just in case. That code is 20209600.

Last edited by a moderator: