+ Reply to Thread
Results 1 to 4 of 4

sickness calander - counting repeated days

  1. #1
    Registered User
    Join Date
    04-05-2017
    Location
    London, England
    MS-Off Ver
    13
    Posts
    1

    sickness calander - counting repeated days

    hello all,

    new here and have been looking through the forums to see if there was something similar to the problem i am having. i have a calander (see attached) and i wish to count the number of occurances (sickness) an individual has off over a year period, but looking at those particular days they have off as sick - how many mondays they have off or how many fridays they have off.

    would also be nice to count number of days since their last sickness days away from work.

    anyhelp would be appreciated.

    thanks

    sanderosa.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,173

    Re: sickness calander - counting repeated days

    your structure , tho good for humans to read, is not good for computers.

    your data should have been stored:
    [name], [date] , [hrs], [OffType] (where OffType = Vaca, PT, SICK,etc)

    then you can run pivot tables and get the reports you want.

  3. #3
    Spammer
    Join Date
    02-27-2018
    Location
    Sydney, Australia
    MS-Off Ver
    2016
    Posts
    10

    Re: sickness calander - counting repeated days

    Hmm, I didn't see such issue, probably its something new

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: sickness calander - counting repeated days

    Hello sanderosa and Welcome to Excel Forum.
    Try the following, as modeled in columns NN:NP of the attached file:
    1) For sick days on Mondays: =SUMPRODUCT((K11:NK11="s")*(WEEKDAY(K$10:NK$10)=2))
    2) For sick days on Fridays: =SUMPRODUCT((K11:NK11="s")*(WEEKDAY(K$10:NK$10)=6))
    3) For days since last sick day*: =IFERROR(TODAY()-LARGE(IF(K11:NK11="s",K$10:NK$10),1),"")
    *Indicates an array entered formula which needs to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [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
  2. Replies: 15
    Last Post: 06-17-2016, 01:59 AM
  3. Pivot chart, display results repeated within 7 days
    By Twizik in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-24-2014, 11:44 AM
  4. [SOLVED] Count Sickness days excluding weekends
    By Cliff Gathern in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 04:18 AM
  5. Counting Repeated Words in a Cell
    By aalbers in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-17-2013, 05:32 AM
  6. Excel 2007 : selective counting of repeated elements
    By buhnen in forum Excel General
    Replies: 7
    Last Post: 06-23-2010, 06:07 PM
  7. Replies: 9
    Last Post: 09-03-2007, 08:26 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