+ Reply to Thread
Results 1 to 18 of 18

Counting occurrences of sickness in a roster across a year

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    Wales
    MS-Off Ver
    97-2003 Excel
    Posts
    5

    Counting occurrences of sickness in a roster across a year

    Hey, I hope I've placed this correctly.

    In short, I am able to extract employee roster data from a database into Excel (2003). A simple example of a 2-week roster would be 'Duty/OFF/SICK/SICK/SICK/OFF/SICK/SICK/Duty/OFF/Duty/Duty/Duty/Duty'


    I would like to find a way for Excel to count the two periods of sickness which are currently separated by an OFF day as one period of sickness.


    Could someone possibly offer some direction as to which functions/formulas could be used to scan a whole year and count occurrences of sickness which may be separated by OFF days, please? Thanks very much for any thoughts.


    Thomas

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: Counting occurrences of sickness in a roster across a year

    Seeing as you seem only to have access to 2003, would it be at all practical to have a helper row for each person?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-31-2019
    Location
    Wales
    MS-Off Ver
    97-2003 Excel
    Posts
    5

    Re: Counting occurrences of sickness in a roster across a year

    Hi and thanks for your reply. Much appreciated.

    Actually, I have Excel versions later than 2003 -- using 2013 at the moment (apologies for getting that wrong).

    I'm sorry, I don't understand what a 'helper row' is. The current format, extracted from the database looks like this>

    ID/Name/Location/Jan1/Jan2/Jan3 etc.
    232/Nightingale, Florence/Manila/SICK/OFF/SICK etc.

    It would be very useful if Excel could count the sick days as one occurrence if they are separated by an OFF day (as days off are left on the roster, even if they are used as sick). Then, across a year, a total number of occurrences of sick could be counted, somehow.

    Thanks for any help you could offer.
    Thomas

  4. #4
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Counting occurrences of sickness in a roster across a year

    You may use SUBSTITUTE() to remove OFF/ away from your exported data.
    Could you please upload a sample or mock up data? (and output that you want)

    Regards.
    My English is very poor, so please be patient >_<"

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,891

    Re: Counting occurrences of sickness in a roster across a year

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does indicates older versions but you can add the recent ones.
    Do you sill use 97/2003 ? Some functions in 2013 didn't exist at the time
    Thanks

  6. #6
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    169

    Re: Counting occurrences of sickness in a roster across a year

    Hi ya! I'm not one of those smart Excel guys, and hopefully they'll help with what you need. But, in the meantime, this kinda works. (see attachment)
    Good Luck!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    169

    Re: Counting occurrences of sickness in a roster across a year

    Or another way I could do it . . . see attachment
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-31-2019
    Location
    Wales
    MS-Off Ver
    97-2003 Excel
    Posts
    5

    Re: Counting occurrences of sickness in a roster across a year

    Here is an example spreadsheet
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-31-2019
    Location
    Wales
    MS-Off Ver
    97-2003 Excel
    Posts
    5

    Re: Counting occurrences of sickness in a roster across a year

    Hi menem,

    I've uploaded a sample spreadsheet, showing a preferred outcome 'Blocks' column. I'm not sure if Excel can do what I would like. I think Excel works in a different way to the database that I am using to store the rosters.

    Thanks,
    Thomas

  10. #10
    Registered User
    Join Date
    10-31-2019
    Location
    Wales
    MS-Off Ver
    97-2003 Excel
    Posts
    5

    Re: Counting occurrences of sickness in a roster across a year

    Hey Greg, thanks for your example. Could I ask, please - how did you get Excel to count 3 periods of SICK from the example roster? Thanks again, T.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: Counting occurrences of sickness in a roster across a year

    Quote Originally Posted by GregM56 View Post
    Or another way I could do it . . . see attachment
    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  12. #12
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Counting occurrences of sickness in a roster across a year

    As a develop of formula in E6

    1st step : concatenate all list
    Please Login or Register  to view this content.
    at this point if you can use TEXTJOIN ( that avilable on Excel2016 and by some add-ins ) your code will be
    =TextJoin( "" , True , F6:AB6 )

    2nd step : remove 'OFF' away
    Please Login or Register  to view this content.
    3rd step : reduce connect of SICKSICK.... become one as a new of token ; in this sample I use # ( choose any char that not presented in list )
    Please Login or Register  to view this content.
    You can see I've subtitute it's for 3 times as different [ SICK -> # , ## -> # , ## -> # ] to reduce all SICK to single of #

    4th step : count that token (final step)
    Please Login or Register  to view this content.
    Regards.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,482

    Re: Counting occurrences of sickness in a roster across a year

    Whilst it returns a 3, in row 8 the answer is 5 when it should be 1? SO I am not sure it answers the question

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,815

    Re: Counting occurrences of sickness in a roster across a year

    Perhaps this array formula:

    =COUNT(1/FREQUENCY(IF(F21:AB21="SICK",COLUMN(F21:AB21)),IF(F21:AB21="DUTY",COLUMN(F21:AB21))))
    Rory
    I drink, and I know things

  15. #15
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    169

    Re: Counting occurrences of sickness in a roster across a year

    I'm sorry. I assumed that anyone who would see any part of this would see all replies in the order they were posted.

  16. #16
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Counting occurrences of sickness in a roster across a year

    After re-check it's seem that my solition not working for a long period sick. >_<"

    Sorry.

    Except in crease of subsitute as

    E6
    Please Login or Register  to view this content.
    Regards.
    Last edited by menem; 11-07-2019 at 03:51 AM. Reason: Add solition

  17. #17
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Counting occurrences of sickness in a roster across a year

    The other solution is use UDF, I'm not sure that you can use VBA or not. >_<"

    Please Login or Register  to view this content.
    Usage is =SickCount( Range that you want to check )
    E6
    Please Login or Register  to view this content.
    Regards.

  18. #18
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Counting occurrences of sickness in a roster across a year

    After saw Greg's solution, suddenly I think this out, thanks to Greg. ^_^
    E6
    Please Login or Register  to view this content.
    if you can use textjoin
    Please Login or Register  to view this content.

    Regards.

+ 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. Replies: 1
    Last Post: 02-13-2019, 10:00 AM
  2. [SOLVED] sickness calander - counting repeated days
    By sanderosa in forum Excel General
    Replies: 3
    Last Post: 03-01-2018, 04:30 PM
  3. [SOLVED] Help with Counting Number of Sickness/Lateness over a month on Summary
    By MrAshy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-09-2018, 01:29 PM
  4. Counting occurrences not values.. Counting blocks of words in columns
    By Flydd in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-01-2016, 06:42 PM
  5. [SOLVED] Creating a roster that counts occurrences of names in raw data
    By ozymandias in forum Excel General
    Replies: 6
    Last Post: 11-28-2014, 08:27 PM
  6. Fire Department roster from year to year
    By Brellian in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2014, 09:02 PM
  7. Counting year occurrences in Excel 2003
    By Ann20 in forum Excel General
    Replies: 2
    Last Post: 07-19-2010, 11:29 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