Forum Discussion
Worksheet and import data
eoghan61 As a variant to DexterG_III 's solution, I chose another route.
First of all, the way this schedule is set up, the 10th day of the month can only occur in columns K:Q. So there is no need to fill the entire Pay Days row with formulae.
Secondly, I though it would be easier to calculate a list of pay dates like in the picture below:
The formula takes the 10th in any month and deducts the result of checking if the WEEKDAY number is 7 (Saturday) or 1 (Sunday), returning either 1 or 2, otherwise 0.
Now you can simply test if the dates on rows 12, 16, 20 etc. in columns K:Q are found in the list of PayDays (a named range).
=IF(ISNUMBER(MATCH(M12,PayDays,0)),"p","")
See attached.
- Oh now that does the trick thanks and DexterG_III thanks also for the tips. My next question is with the schedule in the holidays sheet is it possible to make it somewhat smart so that if I create a rota for say next year could it automatically adjust based on the sheets name?
All i need to fix now is getting my shift patterns to line up correct they seen to be running off pattern in some areas so some reasoneoghan61 Don't understand the question, sorry. What do you mean by "making holidays somewhat smart"? Furthermore, it's probably a bad idea to want to create something that automatically rolls forward into another sheet. I believe the schedule is already complicated as it is right now.
But perhaps you can just add months below the current months and hide the ones that have passed. E.g. copy rows 12:14 and paste them to rows 60:62 and update the formula in A60 so that it returns January 2023.
And with regard to the shifts going off pattern, I can't really tell where that happens.
This is my ultimate goal hopefully this help you understand what i would like to do.
correction the pattern is running fine i think 😉
What I would like to do is make this rota generator so to speak available for the reset of my team and teams that work a different shift pattern so that if we create a new sheet it looks at the previous year and automatically continues the pattern including updating the dates of pay if that makes sense
