Help recording sickness

[CrankyLemming]

I have been asked to set up a table to record when staff need to be
interviewed about excessive sickness. Yay me.

Our rules are that, three periods of sickness (duration doesn't
matter) within the last _rolling_ six months, means a chat with the
manager.

What I thought to do was 5 columns:
Col A with name;
Col b with most recent sick date;
Col c, the previous sick period;
Col d with the sick period before that; and
Col e, the action to be taken.

I can't work out a formula to look at the dates and decide whether
they all fall within the 6 months of the 'current' date (ie, the most
recent sick date for each line, Col B)

Can anyone advise me on the best way forward, as I'm hopeless on VB

Thanks in advance

Steve

 

[Arvi Laanemets]

Hi

An example

You have a worksheet
SicknessTable: Name, Start, End
where you enter start and end dates for every sickness period for employees.
Create 2 named ranges (Insert.Name.Define)
SickEmployee=OFFSET(SicnessTable!$A$2,,,COUNTIF(SicnessTable!$A:$A,"<>")-1,1
)
SicknessEnd=OFFSET(SicnessTable!$C$2;;;COUNTIF(SicnessTable!$A:$A;"<>")-1;1)

On another worksheet, p.e.
Employees: Name, SickInLast6Month
enter all employees (exactly same names as in SicknessTable) into Name
column
Into cell B2 enter formula
=SUMPRODUCT((SickEmployee=A2)*(SicknessEnd>=DATE(YEAR(TODAY()),MONTH(TODAY()
)-6,DAY(TODAY()))))
and copy down as much rows as you have employees

 

[Jim]

If you have the Analysis Toolpak (Tools>addins) loaded, you may use:
=EOMONTH(TODAY(),-6)

 

[Guest]

I thought I already posted this, but just in case.

The following formula should give you what you need base on your example.


=IF(AND(TODAY()-B1<182,TODAY()-C1<182,TODAY()-D1<182),"Meeting Required", "No Action Required")

The IF statement checks if all of the past three sick dates were in the past six months (182 days).


Good Luck,
Mark Graesser
(e-mail address removed)

----- CrankyLemming wrote: -----

I have been asked to set up a table to record when staff need to be
interviewed about excessive sickness. Yay me.

Our rules are that, three periods of sickness (duration doesn't
matter) within the last _rolling_ six months, means a chat with the
manager.

What I thought to do was 5 columns:
Col A with name;
Col b with most recent sick date;
Col c, the previous sick period;
Col d with the sick period before that; and
Col e, the action to be taken.

I can't work out a formula to look at the dates and decide whether
they all fall within the 6 months of the 'current' date (ie, the most
recent sick date for each line, Col B)

Can anyone advise me on the best way forward, as I'm hopeless on VB

Thanks in advance

Steve

 

[CrankyLemming]

=IF(AND(TODAY()-B1<182,TODAY()-C1<182,TODAY()-D1<182),"Meeting Required", "No Action Required")

Okay, thanks everyone for the help. I've got it up and running now.

Steve