Hi,
I am trying to identify unique patterns to ultimately count how many distinct absences a person has had, the start and end dates of those absences and the number of hours associated with each distinct absence. I am open to helper columns or vba - although I'm just learning vba myself. A sample data file is attached.
Problem:
I have a database of multiple absences sorted by individual along with the # hours for each absence and the associated start and end dates of the absence. The solution I am trying to achieve is to identify unique instances of absence ( which may be made up of multiple dates) and the total number of hours for each of these absences.
Some contraints I am working with:
- if the start date of one leave is within <=15 days of the end of the prior leave, the absence is considered continuous. If the time between these leaves is >15 days this is considered a new absence period.
- often, there can be two lines with same start and end date for that person as the hours were classified into different types. The types are too numerous and varied to use them as a reliable filter. Unfortunately, I can't just ignore the lines with duplicate dates entries as I need to add the hours from each entry into my total.
End result:
To reduce the database into a separate sheet containing only one line per absence with the start and end dates of that absence period and the total hours associated with that time period. I was planning on filtering the data once all the calculations were done but if vba can help me pull out those unique lines, all the better.
What I've tried:
- Getting there in steps using helper columns
A column to determine the time duration between the end date of one line and the start date of the next based on the Identifier being equal.
=IFERROR(IF(A3=A4,SUM(C3-B4),0),"end of record")
this works well but then I haven't been able to find the right formula to create the rules around how to determine which lines of Hours to add together based on the result
I have also been able to use an array to find the min and max dates by identifier and put them in seprate columns but this doesn't work when a unique identifier has two or three distinct absences (>15 days apart)
{=MIN(IF(A:A=A3,B:B))} and {=MAX(IF(A:A=A3,C:C))}
Thanks in advance for your help.
Bookmarks