Highlighting specific days on a calendar based on work patterns

Hi,

I have an attendance tracking spreadsheet that I put together for my manager a couple of years ago so that he can see at a glance who’s in the office each day, and also record absences for various reasons. I couldn't find any templates that met my needs at the time. It’s essentially a very long spreadsheet like a calendar with the dates across the top and each employee’s name down the side – one row for each employee, and it’s split into “flexi-periods”, the 4 week blocks of time that are corporately used for recording time, with hyperlinks as short-cuts to go to the different time periods.

It picks up each employee’s working pattern from a configuration page that has the days of the week across the top and the employees’ names down the side, with a ‘Y’ (for yes) for the days an employee works. This automatically fills in on the attendance tracker the days each employee’s in the office, but then the manager can overwrite it using a drop-down list to change it to a variety of reasons for absence.

It works well for full time employees, and for some part-time employees, but there are some who work a specific day on alternate weeks. To get it to fill in the working pattern for each employee for the year I simply have to enter the start date for the year on the configuration page, and it fills everyone’s working patterns in, but with the ones who work a specific day on alternate weeks, I have to go through it and manually alter whichever day they work alternate weeks for the whole year. Usually it’s where it’s a job-share post, and one works the beginning of the week – Monday, Tuesday and alternate Wednesdays – and the other works the end of the week – alternate Wednesdays, when their job share partner’s not in, and Thursday and Friday. It’s further complicated by the fact that if there’s a public holiday in the week, neither works the alternate day and they split the remaining days in that week between them, and then the following week go back to the regular pattern of working alternate Wednesdays each.

It's a pain having to go through and manually change those at the start of every year (especially as currently there are 4 employees with that working pattern), and I’ve been wracking my brains trying to figure out it there’s a way I can automate it. I did think that I could possibly somehow make each week a table and name them by their week number, and somehow set something up (no idea what or how) so that the Wednesday for the employee is only filled in for either odd or even weeks, but the fact that if there’s a public holiday in the week the rule doesn’t apply would throw it all out of whack. It's further complicated because any formulas wouldn't be the same for every employee, and though at the moment there are 4 employees on this kind of work pattern, in the future there could potentially be more or fewer. I'm guessing that to future-proof it I'd need to use the same formula for all employees and add it into the formula that currently highlights the days, additonally testing whether the week's odd or even (and I suppose to start off, which variation of the work pattern they start on at week 1), but it would need to leave the days for full-time employees, or those on part-time fiexed days, unaffected. I haven't a clue where to start on it!

I’ve attached an anonymised copy of the spreadsheet so that you can see what it’s like. The working patterns it's showing are the result of the formulas using the working day information from the configuration page. Normally there is protection on the attendance tracker page to protect the formulas, but I've taken it off for this example (although actually if my manager enters an absence reason, it overwrites the formula anyway - the formulas just save either me or my manager having to input each employee's normal working pattern). For w/c 24/2/20 and w/c 2/3/20 I've edited it manually to show the employees who work alternate Wednesdays (Persons 2 and 3, and Person 8 and 11. The following week shows how you can use drop-down lists to enter absence reasons. If you click the 'Flexi-period 3' hyperlink you'll see that Excel's also automatically put in public holidays, which again it picks up from a list on the configuration page, and it's these days that really mess up me working out who's working which Wednesdays!

Is this do-able? Any suggestions as to how I may be able to accomplish this would be gratefully received.

for p2
how can we know there is an annual leave?
for halfweek:
=VLOOKUP($H11,Configuration!$D$3:$L$22,2 +WEEKDAY(Y$8,2),)

btw row10 this way is better

Hi, thanks for taking a look at this.

Originally Posted by tim201110

for p2
how can we know there is an annual leave?

The manager selects the appropriate absence code from the drop-down list. The formula essentially fills in everyone's work pattern at the start of the year, saving either myself or the manager from having to enter it manually, but the manager can manually overwrite the work pattern to show absences from the office.

for halfweek:
=VLOOKUP($H11,Configuration!$D$3:$L$22,2 +WEEKDAY(Y$8,2),)

btw row10 this way is better

I'm sorry, I don't understand. The VLOOKUP seems to just put in the work pattern the same as the existing formula, but I don't understand what you mean about row10.