Counting Empty Dates within a Range?

[Gina]

I am going to have a task in about a week where I'll be asked to look at a
spreadsheet full of safety data, and pull from this spreadsheet the list of
departments that deserve safety awards. They earn these awards by having no
lost-time injuries for a period of 30, 60, or 90 consecutive days.

The records that are kept of individual safety events. For Instance:

Joe Smith, Mechanic, Operations Department, 8/14/08, Lost Time Injury
Jane Doe, Administrative Assistant, Facilities Department, 7/12/08, Lost
Time Injury
Bill Smith, Mechanic, Operations Department, 3/16/08, Lost Time Injury.


Does anyone know of a way where a big list of individual incidents could be
queried to show when there are gaps are 30, 60, or 90 days?

Gina

 

[Allllen]

I suggest you sort your data by department, then by date.
You can add a new column next to the dates where you do something like
= D2-D1 (and format the cell as a simple number) [assuming you have the
dates in column D]

This will give you the number of days between successive incidents within a
department.

From there you could filter on this new column for any values greater than
90, 60, 30 etc.
Or you can write another formula to say =IF(E1>90, "Great",IF(E1>60,"V
Good",IF(E1>30,"Good","""))). And then count the number of greats, v goods
etc by department

 

[Gina]

I suggest you sort your data by department, then by date.
You can add a new column next to the dates where you do something like
= D2-D1 (and format the cell as a simple number) [assuming you have the
dates in column D]

This will give you the number of days between successive incidents within a
department.

From there you could filter on this new column for any values greater than
90, 60, 30 etc.
Or you can write another formula to say =IF(E1>90, "Great",IF(E1>60,"V
Good",IF(E1>30,"Good","""))). And then count the number of greats, v goods
etc by department



--
Allllen

I am going to have a task in about a week where I'll be asked to look at a
spreadsheet full of safety data, and pull from this spreadsheet the list of
departments that deserve safety awards. They earn these awards by having no
lost-time injuries for a period of 30, 60, or 90 consecutive days.

The records that are kept of individual safety events. For Instance:

Joe Smith, Mechanic, Operations Department, 8/14/08, Lost Time Injury
Jane Doe, Administrative Assistant, Facilities Department, 7/12/08, Lost
Time Injury
Bill Smith, Mechanic, Operations Department, 3/16/08, Lost Time Injury.


Does anyone know of a way where a big list of individual incidents could be
queried to show when there are gaps are 30, 60, or 90 days?

Gina

Thank you very much Allen, I will give this a try.
Gina

 

[M Kan]

You could also run a pivot table by department (or person) as a Row element
and a count of dates as the data element. Copy and Paste the data and then
calculate the difference in dates as Alllen suggested.