Highlighting multiple triggers

[melhay]

I am really starting to wish I hadn’t suggested our sickness recordin
spreadsheet could be improved…
I am fairly confident with Excel, and can see the possibilities – an
will attempt VBA if necessary (I suspect it may be but I’m not reall
familiar with it)

I have a spreadsheet to track sickness with a summary sheet as the firs
worksheet and subsequent worksheets for each month. Sickness is recorde
as 1, 0.5 or 0 where 1 is a full day sick, 0.5 is half day and 0 is
non-working day (some staff work shifts). The monthly sheets are set u
with columns A & B holding staff names, C with a formula to display th
total value from range D:AH (this being each day of the month).
The summary sheet pulls values from Column C for each month, and als
has the summary data from the previous year. I was intending to directl
input the values from last year as the old sheet is not directl
compatible. The year runs from April to March.

I need to be able to flag the following 3 'triggers':
Sick for 5 or more working days in a row
Sick for 3 working days over the last rolling 3 month period
Sick for a total of 5 or more working days over a rolling 12 mont
period

I am thinking that the monthly worksheets could have conditiona
formatting where if 5 of the previous 7 cells are not blank the tex
turns red (some staff work weekend so they may or may not need to b
included) – but due to the varying shifts and rolling requirements I a
tying myself up in knots!

I would really appreciate any help anyone can give me – unfortunately m
ambitions don’t match my expertise – yet! Hopefully this gives enough o
an idea of my aims – if not please let me know.

Thank you in anticipatio

 

[GS]

Food for thought...

You might find it easier to manage if you add 12 month columns to the
right og AH, and collect monthly data there. This, of course, would
require each entry to include a date so each month column could collect
only its entries.

Alternatively, you could continue using a 'working' sheet month to
month, but use only one sheet for the current fiscal year where you
collect data for each month over 12 columns. This will still provide
familiar task processing but allow you to store multiple years in a
single file (1 sheet per fiscal year).

If you organize the headers for the month columns as 3 character
abbreviations then you can store the current month on the 'working'
sheet and use a macro to 'post' the data to its respective column on
the fiscal year sheet, clear the 'working' sheet, and set the next
month so you're ready to go.

The macro can also handle the highlighting of consecutive days.

-OR-
I'm assuming you also want to record the actual days (on fiscal sheet)
that someone is sick, and so you could also set up day rows for each
month and worker columns on a single worksheet for each fiscal year.
Then everything is in one place and you could use outlining with totals
for each month in the top row so you can see them when the group is
collapsed. I do something similar where I have a sheet set up for each
day of a fiscal year on contiguous rows, and the tracking criteria in
columns starting in "C". Column "A" contains formulas to automatically
populate the entire sheet with dates when the fiscal start date is
entered in month1. Column "B" is set up to display the 3 character
abbreviation for the day of its respective value in column "A". The
days are configured same as a calendar, so if your fiscal year starts
April 1st then you'd enter 4-1 into the 1st cell of month1 because this
year April 1st is a Sunday. If it was a Tuesday then you'd enter 4-1 in
the 3rd cell of month1, leaving the previous cells blank. Feb is
configured to leave the 29th cell empty if not a leap year.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[melhay]

Thank you for taking the time to respond. I can see a couple of issue
(staff work out in the field and we may not know they are off sick unti
some time the next month, so clearing the sheet could be problematic
and we need to collate half days as well, so a date may not work) - bu
I will certainly see if I can incoporate some of your concepts.

;1583590']Food for thought...

If you organize the headers for the month columns as 3 character
abbreviations then you can store the current month on the 'working'
sheet and use a macro to 'post' the data to its respective column on
the fiscal year sheet, clear the 'working' sheet, and set the next
month so you're ready to go.

The macro can also handle the highlighting of consecutive days.

-OR-
I'm assuming you also want to record the actual days (on fiscal sheet)
that someone is sick, and so you could also set up day rows for each
month and worker columns on a single worksheet for each fiscal year.
Then everything is in one place and you could use outlining with total

for each month in the top row so you can see them when the group is
collapsed. I do something similar where I have a sheet set up for each
day of a fiscal year on contiguous rows, and the tracking criteria in
columns starting in "C". Column "A" contains formulas to automatically
populate the entire sheet with dates when the fiscal start date is
entered in month1. Column "B" is set up to display the 3 character
abbreviation for the day of its respective value in column "A". The
days are configured same as a calendar, so if your fiscal year starts
April 1st then you'd enter 4-1 into the 1st cell of month1 because thi

year April 1st is a Sunday. If it was a Tuesday then you'd enter 4-1 i

the 3rd cell of month1, leaving the previous cells blank. Feb is
configured to leave the 29th cell empty if not a leap year.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[GS]

melhay explained on 3/12/2012 :

Thank you for taking the time to respond. I can see a couple of issues
(staff work out in the field and we may not know they are off sick until
some time the next month, so clearing the sheet could be problematic,
and we need to collate half days as well, so a date may not work) - but
I will certainly see if I can incoporate some of your concepts.

You could easily include a column for duration that uses hours or
decimal fraction of a workday's normal shift duration. For example...

Hours:
4.5

Days: (based on 8-hour shift)
=8/4.5

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[melhay]

Thanks Garry!

;1599716']melhay explained on 3/12/2012 :-

Thank you for taking the time to respond. I can see a couple o issues
(staff work out in the field and we may not know they are off sic until
some time the next month, so clearing the sheet could be problematic,
and we need to collate half days as well, so a date may not work) but
I will certainly see if I can incoporate some of your concepts.-

You could easily include a column for duration that uses hours or
decimal fraction of a workday's normal shift duration. For example...

Hours:
4.5

Days: (based on 8-hour shift)
=8/4.5

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio