# Get number of sick periods for each month for Bradford score

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

#### Thread Information

##### Users Browsing this Thread

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