hi again,
thanks for the help so far, i have one more challenge if you're up for it.
first it might help if i explain what i'm trying to do and what i've done so far. i and my colleagues work an unusual shift pattern over a 28 day cycle( starting on a thursday it goes: 7 off, 4 on, 3 off, 2 on, 2 off, 3 on, 3 off, 4 0n.)
i want a year planner for the next ten years that will show what i am doing each day (either: working, rostered off, holiday, sick, lieu day or overtime). i then want cells at the side of the planner that show: how many holidays i have left in this holiday year, how many lieu days i have left, how many days sickness i have had in the past year. and i want these cells to update automatically as i take holidays or go sick etc. what i have done is make a year planner using 2 cells for each day, one on the left containing the date and one to the right of it for data entry (w for work, s for sick, h for holiday etc.)each 28 day cycle uses 56 clls in one row on the worksheet. i have labelled every working day with a "w" and made the cell yellow. so i can see at a glance when i am working and when i am off. i have used conditional formatting so that if i enter an "s" or "h" or "l" in a yellow cell it will change colour to red, green or blue so i can easily see if i have been sick or had a holiday etc. i have also, with your help, got the cells that display the running totals of sickness, holidays etc. working. the last thing i need is a cell that display the number of periods of sickness in the previous 12 months. this needs to update every day just like the number of sick days formula. the reason for this, is that if someone has three or more periods of sickness in the previous 12 months they are disciplined, and lose their sick pay for 6 months. it is quite difficult to define a period of sickness for these purposes. basically it is a day (or days) off sick, followed by a day of work. so if i went sick on a wednesday when i was rostered on, then i was rostered off for three days and then was sick for the next 2 days when i was rostered on, it would only be one period of sickness. but if i went sick on a wednesday when i was rostered on then i was rostered off for three days then returned to work for one day then went sick the following two days i was rostered on, that would be 2 periods of sickness. i hope i have explained this ok, basically the first day i work after i have been sick marks the end of a period of sickness, rostered off days are completely ignored for this purpose.
is it possible to do what i want with the worksheet i have created? or would it be possible if i made some modifications to the layout?
thanks stuart
