+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    11-12-2014
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    47

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

  2. #2
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    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. #3
    Registered User
    Join Date
    11-12-2014
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    47

    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. #4
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    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. #5
    Registered User
    Join Date
    11-12-2014
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    47

    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. #6
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    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. #7
    Registered User
    Join Date
    11-12-2014
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    47

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

  8. #8
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

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

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to get specific absent dates from employees attendance data
    By dineshjangir in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-13-2019, 02:50 PM
  2. Count number of overlapping days in multiple date ranges
    By tarsonis in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-08-2015, 03:22 PM
  3. Count for present & absent days
    By Ajit Munj in forum Excel General
    Replies: 8
    Last Post: 10-08-2013, 01:13 AM
  4. [SOLVED] How to identify absent employees across 2 worksheets with results on a 3rd worksheet
    By jshimko in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2013, 12:14 PM
  5. Number of days absent in the past 90 days
    By pumpkinalden in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-19-2012, 08:01 PM

Bookmarks

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