Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates within a specified range | Excel Discussion (Misc queries) | |||
counting valid dates in a range of cells | Excel Worksheet Functions | |||
Counting dates in a RANGE (yargh!) :) | Excel Worksheet Functions | |||
Counting Dates in a Range | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |