Sickness dates
 

I have a spreadsheet which shows staff sickness each month. I would like to
classify whether each absence is 'short term' or 'long term' in column F.
Any absence which is continuous 20 days or more would be classed as long term.

Here's an example

A B C D
E F
Ref No Name Start Date End Date Total
Length
3421 Jones 21/11/09 30/11/09 10
Long Term
3421 Jones 01/12/09 17/12/09 17
Long Term
8756 Sue 02/07/09 08/07/09 7
Short Term


In this example, Mr Jones has had 27 days continous absence in total and so
this would be a long term absence. The text in column F is what I want to
see as a result.

Any help would be really useful.

Sickness dates
 

One way is to use SUMIF to sum the "Totals" col E by the names in col B, then
check the result vs the criteria of 20 days = Long Term
Put this in F2:
=IF(SUMIF(B:B,B2,E:E)20,"Long Term",IF(SUMIF(B:B,B2,E:E)0,"Short Term",""))
Copy down. Success? celebrate it, hit YES below
--
Max
Singapore
---
"Charles S" wrote:
I have a spreadsheet which shows staff sickness each month. I would like to
classify whether each absence is 'short term' or 'long term' in column F.
Any absence which is continuous 20 days or more would be classed as long term.

Here's an example

A B C D
E F
Ref No Name Start Date End Date Total
Length
3421 Jones 21/11/09 30/11/09 10
Long Term
3421 Jones 01/12/09 17/12/09 17
Long Term
8756 Sue 02/07/09 08/07/09 7
Short Term


In this example, Mr Jones has had 27 days continous absence in total and so
this would be a long term absence. The text in column F is what I want to
see as a result.

Any help would be really useful.

Sickness dates
 

=IF(SUMIF(B:B,B2,E:E)20,"Long Term",IF(SUMIF(B:B,B2,E:E)0,"Short Term",""))

Oops, change the "20" in the formula to: =20
(you indicated: 20 days or more)
--
Max
Singapore
---

Sickness dates
 

Max - thank you for replying. Just checking about continuous dates - if
Jones took a further 1 day absence in January 2010, I need this to show as
short term only. Is there a formula to cope with this as well?

"Max" wrote:

=IF(SUMIF(B:B,B2,E:E)20,"Long Term",IF(SUMIF(B:B,B2,E:E)0,"Short Term",""))

Oops, change the "20" in the formula to: =20
(you indicated: 20 days or more)
--
Max
Singapore
---

Sickness dates
 

You'd probably need to add a new column to your data set to distinguish/flag
non continuous from continuous dates cases, and populate this col manually.
Then use sumproduct (instead of sumif) to take care of the multi-criteria
summing.
--
Max
Singapore
---
"Charles S" wrote:
Max - thank you for replying. Just checking about continuous dates - if
Jones took a further 1 day absence in January 2010, I need this to show as
short term only. Is there a formula to cope with this as well?