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

1. ## 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.

2. ## Re: Count number days absent for different employees with multiple date ranges.

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

3. ## Re: Count number days absent for different employees with multiple date ranges.

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...

4. ## Re: Count number days absent for different employees with multiple date ranges.

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.

5. ## Re: Count number days absent for different employees with multiple date ranges.

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.

6. ## Re: Count number days absent for different employees with multiple date ranges.

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.

7. ## Re: Count number days absent for different employees with multiple date ranges.

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.

8. ## Re: Count number days absent for different employees with multiple date ranges.

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1