Hello,
I have a list (several hundreds of lines) of employees who have been ill during say, the past three months. It contains a start date, an end date, the number of days and an employee ID-number. I would like to see for each employee, the number of accumulated, consecutive days of illness.
So, if someone has been ill from 9/9/2019 until 7/10/2019 and then was prolonged from 8/10/2019 up to 11/10/2019 it should add up to 33 days. If some time later, this person has also been ill from 14/10/2019 up to 18/10/2019 this only represents 4 days.
I tried different approaches but until now, none of them worked.
I would very much appreciate if someone could find a solution!
Kind regards,
Nadine
Try the attached. It is assuming that the rows showing the overlapping dates would be in order.
Column F =IF(B2=A3-1,"Con","Not"). Checking if dates overlap
=IF(B2=A3-1,C2+C3,IF(F1="CON","",C2)). Adds days together if overlap, otherwise just the number in C (unless previous day was consecutive.
Only had a quick check but seems to be okay
Hi Purfleet,
thank you for the answer but I believe it's not quite right. When for example, in cel A12 you enter 6/1/2020 and in cell B12 you enter 8/1/2020, it adds up the days although two different persons are concerned (see employee ID). The employee ID should be taken into account as well.
Also if you have three consecutive periods, it adds up period one and two and then again period two and three....
Kind regards
Nadine
Sorry didn’t think about employee - the attached fixes that issue.
We could extend the formula to work for 3 occurrences, but then I suppose 4 are possible and so on and the formula would soon get unwieldy
Hello Purfleet,
Thanks for your help, it sure helps a lot! But as you already mentioned, it is indeed possible to have more, say 6 or 7 lines of illness per person. In rows 37 up to 39, there are 3 consecutive periods that should add up 51 but the number of days in columns H does not match....
Kind regards,
Nadine
If your data is sorted like the sample, and you don't mind the consecutive totals appearing at the end of the sequence rather than the start, you could use:
=IF(IF(D2=D3,A3-B2,"")=1,"",SUM(C$2:C2)-SUM(E$1:E1))
in E2, and copy down.
Hello Velouria,
I tried the suggested solution and it just works out great! I very much appreciate your help for I would have been struggling (in vain) for a long time!!!
Might I just ask to explain the formula please? So, if the employee ID corresponds in both lines, you substract the second start date from the one in the first line, otherwise, you get a blank cel. But why the '=1"?
and the rest of the formula starting with 'sum' is not clear to me either.....
Kind regards
Nadine
Hi Nadine,
That part works out the number of days between the end date of one row and the start date of the next row, assuming the employee id is the same. If that returns 1, then it's a consecutive absence, so the formula simply returns "" - the total calculation is only done for the last of the consecutive periods.
When the calculation is done, it simply totals up the number of days (for all employees/dates) from the start of the data to the current row, and deducts the totals previously calculated in the formula column, which results in the balance for the current absence period.
Hopefully that makes sense?
Velouria said
If your data is sorted like the sample, and you don't mind the consecutive totals appearing at the end of the sequence rather than the start, you could use:
=IF(IF(D2=D3,A3-B2,"")=1,"",SUM(C$2:C2)-SUM(E$1:E1))
in E2, and copy down.
I love that formula - if within and if and couple expanding range.
Beautiful
Hello Velouria,
I did not understand the formula quite well and tried to simplify it and the results are the same:
I replaced your formula
=ALS(ALS(AB2=AB3;H3-I2;"")=1;"";SOM(L$2:L2)-SOM(AZ$1:AZ1))
by
'=ALS(EN(AB2=AB3;H3-I2=1);"";SOM(L$2:L2)-SOM(AZ$1:AZ1))
Thanks again for helping me out!!!!
Kind regards
Nadine
