Counting Empty Dates within a Range?
 

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

Counting Empty Dates within a Range?
 

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(E190, "Great",IF(E160,"V
Good",IF(E130,"Good","""))). And then count the number of greats, v goods
etc by department



--
Allllen


"Gina" wrote:

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

Counting Empty Dates within a Range?
 

"Allllen" wrote:

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(E190, "Great",IF(E160,"V
Good",IF(E130,"Good","""))). And then count the number of greats, v goods
etc by department



--
Allllen


"Gina" wrote:

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

Counting Empty Dates within a Range?
 

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Gina" wrote:

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