+ Reply to Thread
Results 1 to 2 of 2

Get number of sick periods for each month for Bradford score

  1. #1
    Registered User
    Join Date
    08-05-2019
    Location
    uk
    MS-Off Ver
    1906 Office 365 Business
    Posts
    1

    Get number of sick periods for each month for Bradford score

    Hi, I am making a sheet that will allow us to input either sick, unauthorised leave or authorised leave (ul and al) on a yearly calendar and work out a bradford score for each employee.

    I currently have the total days of per month calculated as it was the easy part, code below:
    =SUM(COUNTIFS($H$4:$NI$4,">="&NW$4,$H$4:$NI$4,"<"&EDATE(NW$4,1),$H5:$NI5, {"Sick","UL","AL"}))

    H4 to NI4 is the dates of the year, with one extra for a leap year as it automatically updates the calendar and weekends extra for all this. H5 to NI5 is where we would mark data when needed for employees.

    My problem is when I try to automate counting the total periods for each month as i dont know how to narrow it down to each month and dont want to hard code the values to only look from column H to AL for jan ect... as this would not work on a leap year.

    this is currently my code:

    =SUM(SUMPRODUCT(--(FREQUENCY(IF($H6:$NI6="sick",COLUMN($H6:$NI6)),IF($H6:$NI6<>"SICK",COLUMN($H6:$NI6)))>=1)),SUMPRODUCT(--(FREQUENCY(IF($H6:$NI6="UL",COLUMN($H6:$NI6)),IF($H6:$NI6<>"UL",COLUMN($H6:$NI6)))>=1)),SUMPRODUCT(--(FREQUENCY(IF($H6:$NI6="AL",COLUMN($H6:$NI6)),IF($H6:$NI6<>"AL",COLUMN($H6:$NI6)))>=1)))

    I am new with using formulas so have no idea where to start, have tried looking for answers can never find anything.

    Also it would need to be able to look at the date and check if it is a weekend skip over them so it does not create 2 different periods for being sick on fri and mon. Something like:

    =IF(OR(H$3="Sat",H$3="Sun"),skip..., count...)

    as in row 3 From H to NI (above the formatted date) i have the day of the week it is.

    Thanks in advance

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Get number of sick periods for each month for Bradford score

    Administrative Note:

    Welcome to the forum.

    Although we value privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, etc. will suffice).

    Also knowing your Office version will help us to tailor a solution for you - please add this, too.

    Thank you for helping us to help you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. Calculating a Bradford score from a list of dates.
    By jcborland in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2018, 09:40 AM
  2. Replies: 9
    Last Post: 09-22-2015, 08:47 AM
  3. Separate number of days in month into two payroll periods
    By AJ_Webb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2015, 08:47 PM
  4. Calculate sick absence by days per month.
    By Pegs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2013, 06:57 AM
  5. Replies: 6
    Last Post: 11-25-2012, 04:57 AM
  6. Register with Bradford Score
    By mattdrake in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2009, 03:01 PM
  7. Converting a Number score to an equivalent Letter score
    By cgurr1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-24-2008, 12:42 PM

Tags for this Thread

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