+ Reply to Thread
Results 1 to 2 of 2

Calculate sick absence by days per month.

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2003
    Posts
    4

    Calculate sick absence by days per month.

    Hi

    What I need is a formula (or formulae) that will allow me to calculate how many days of sick absence a person has had in each month given a start date and a return to work date. An added complication is that a person is only classed as being sick-absent on the weekdays that they would normally work (not everybody works the 5 day standard week). What I'm looking at is something like this:

    A1 - Date sickness absence began (e.g 10th January 2013)
    B1 - Date person returned to work (e.g 13th April 2013)
    C2-G2 - an "N" if the person does not normally work that day, blank if they do (where c2=Monday, D2=Tuesday etc)
    H2-S2 - these are the cells I want to enter formulae to auto-calculate the number of days the person has had sick that month (H2=Jan, I2=Feb, etc)

    So, if a person only normally works Tuesdays, and was sick from 10th January to 13th of April, the formulae should return 3 for January in cell H2 (as there are 3 Tuesdays between 10th January and the end of the month), 4 in cell I2 (February), 4 in cell J2 (March) and 2 in cell K2 (2 Tuesdays between the beginning of April and return to work on April 13th)

    I've tried looking at ways to achieve this using NetworkDays(), but can't figure out how to break down the number of days by month. Any help would be hugely appreciated.

    Thanks

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate sick absence by days per month.

    Try putting the first of each month in H1:S1, e.g. 1/1/2013 in H1, 1/2/2013 in I1 etc. (you can custom format as mmm-yyyy to display as Jan-2013 etc.) then in H2 use this formula copied across to give the number of days absence in each month based on the days that are worked shown in C2:G2

    =SUMPRODUCT(TEXT(INT((WEEKDAY(MAX($A1,H$1)-{2,3,4,5,6})+MIN($B1-1,EOMONTH(H$1,0))-MAX($A1,H$1))/7),"0;""0"";0")*($C$2:$G$2<>"N"))

    If you upgrade in the future to excel 2010 you can do this more easily with NETWORKDAYS.INTL function
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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