KevinFlame
New Member
I have a report that shows a history of where an employee has been assigned in our business.
Columns B and C have the start and end dates of the record, and column F shows the grade they were during each assignment. However, they may have also been that grade (column F) in a previous record, so I need to find out the total of how long each employee spent in that grade by looking across multiple rows.
I know I can use the MIN() formula to find the oldest date in an array, but what I can't work out is how to find all of the records for each employee, then find all of the records for each particular grade, then find the oldest and newest dates for each of those ranges...
Once I have the dates I can count the number of days between each, to see how long they spent in that grade. Oh, and if the end date is 31/12/9999, then that means they are currently in that post - so I was just going to use the TODAY() function and assume it is active now.
I know this may be difficult to picture, so I have attached a sample file that has two sheets. The first sheet has the raw data, the second an example of what I am trying to do - but without the formulas. I have coloured each band of data I am trying to get the dates for as well, so hopefully that'll help paint the picture.
Hope someone can help.
Columns B and C have the start and end dates of the record, and column F shows the grade they were during each assignment. However, they may have also been that grade (column F) in a previous record, so I need to find out the total of how long each employee spent in that grade by looking across multiple rows.
I know I can use the MIN() formula to find the oldest date in an array, but what I can't work out is how to find all of the records for each employee, then find all of the records for each particular grade, then find the oldest and newest dates for each of those ranges...
Once I have the dates I can count the number of days between each, to see how long they spent in that grade. Oh, and if the end date is 31/12/9999, then that means they are currently in that post - so I was just going to use the TODAY() function and assume it is active now.
I know this may be difficult to picture, so I have attached a sample file that has two sheets. The first sheet has the raw data, the second an example of what I am trying to do - but without the formulas. I have coloured each band of data I am trying to get the dates for as well, so hopefully that'll help paint the picture.
Hope someone can help.
