Count number days absent for different employees with multiple date ranges.

Please find attached a sample file. In column F on the Full time staff sheet, I am wanting a formula to return the numbers of days absent between the specific dates derived from the dates for that employee on the absence sheet. Thanks in advance.

Maybe if you explain the pattern it would be easier to understand what you're looking for.

The is no pattern, the data can be as irregular as it is laid out.

On the Full time staff sheet, the dates from and to dates differ on each line as they are paid at a different rate. The formula I'm looking for will go to determine how much each employee will have deducted based upon the dates they have been absent.

Hope that paints the picture a little clearer...

How is the first answer of 7 determined? If there really is no pattern, Excel won't be able to figure it out. You have to tell it what to look for.

The answer of 7 days relates only to staff number 2 between 1/4/15 to 18/9/15 and is made up (from the absence sheet) 5 days for 1/4/15 to 5/4/14, 1 day for 16/6/15 to 16/6/15 and 1 day for 18/9/15.

Staff_absence_formula.xlsxI see. See attached workbook with formula. This requires that the absences are entered separately for each period so you will see I split the one which spanned from Sept 18 to Sept 19.

That's great, thanks very much for your help. Much appreciated! Will accommodate splitting the absence period dates into separate chunks.

As an added extra, it fell down when there was 3 or more date ranges on the full time sheet, so extended your formula in the attached version which works however number of date ranges there are on the full time sheet.

You're welcome.