Would like to automate creating new 3 shift pattern

Hi Ross,

I looked at this for you some time ago, but didn't get chance to progress very far with it. This time I hope I'll be able to stick with it, but it will take some time to build the necessary formulae, so don't hold your breath !!

Presumably you want to specify the year somewhere and the starting shift in the first week of January and then everything else will slot into place automatically?

I'll give it a go and get back to you - but, my daughters are visiting this weekend from tomorrow, so it is likely to be next week at the earliest.

Pete

Hi Ross,

I see that you have posted the file as an .xls file, although your Excel version is 2007. Is there any chance that this file is likely to be used by someone with XL2003 or earlier? This affects the number of conditional formats that are allowed in each cell.

Pete

Hi Ross,

the womenfolk went out looking for wedding dresses today, so I was able to get stuck in and have finished it off.

Before I release it, though, can you explain what the shaded areas of the calendar represent around Christmas and New Year? Are you closed during these dates, and if so what are the rules that govern your closure? On your attachment you seem to be closed from 24th Dec onwards, but is this always the case? When do you return in the New Year, and does the shift pattern pick up from where it got to when you closed?

Pete

Hi Ross,

thanks for getting back to me. I've attached the file that I completed last week, as I haven't done any more work on it since then. The auto-calendar sheet is protected to prevent any accidental over-writing of the formulae, but there is no password so you can easily un-protect it if you need to. You will notice that I have changed the layout, so that the days of the week are aligned rather than the day number - I think this is easier to follow, and is more like a standard planning chart.

To use the file all you need to do is select the appropriate year in cell U1 (from the drop-down or by typing the year directly), and then specify in cell H6 (the first Friday in January) which shift occurs in the mornings that week - this cell is coloured red until you enter a value of 1, 2 or 3. The calendar will automatically adjust to the year, and display your shift patterns once the first shift is identified.

You will notice if you do a Print Preview that there is enough room on a standard A4 printout to display the first month of the following year, and this is why I asked about the Christmas arrangements. I've not tried to implement those as I have only just seen your response, but the pattern continues at the moment as if there were no shut-down. If I don't get chance to do anymore work on it, you could just delete those bottom few rows if they cause confusion, so that you just have a single year displayed.

Anyway, hope this helps - I'm sure you know what to do if you think this solves your problem.

Pete