Hi,
Here is a best i can do for the breakdown AliGW
This starts on the shifts sheet Cell I3
* Check if A3 is empty, if not continue, else get rota pattern using this forumula and add dummy data to end.
* check if Jan E6 is empty, if not continue, else blank
* using LET define d_patt, first check on Jan sheet to see if an entry has been made for overtime, holiday etc. then get abreviated code, then check to see how many hours in each of the 3 columns on the jan sheet this will make something similar to HOL000
* this then moves onto createing the full length string, first get Rota so will Genereate ROT080 then check to see if any hours for OVT if so then defice OVTxxx if not use OVT000, then move onto the next so SSIxxx if no hours then SSI000 and so forth.
Not sure i have explained enough, but in a nut shell,
Get the Rota Pattern from Teams Tab, BHR4 calculate the date needed and get the 3 shift i.e 080 this should then create ROT080 (which is 8 hours on the late shift, 0 hours on early and night shift)
Then check JanE6 to see if any dropdown selected, get the abreviated code so Holiday = HOl and Overtime = OVT, then the 3 cells before are the hours this will then generate HOL080 this is then used to create a full string. if any of the abreviations are not selected then use 0's so if no MAT then it will be MAT000m this is then joined togehter for example
ROT080OVT000SSI000SSO000SDS234HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000
This shows ROT080 which is 8 hours on late shift, then SDS has 234 and all the others have nothing 000
Hope i have explained enough.
Thanks
Bookmarks