Closed Thread
Results 1 to 18 of 18

How to count consecutive dates for each employee from a listing of absence dates?

  1. #1
    Registered User
    Join Date
    01-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    5

    Question How to count consecutive dates for each employee from a listing of absence dates?

    Hello,

    First of all, I am very excited to have found this site! I've reviewed some threads and the answers/help given are brilliant I'm hoping to seek a bit of your time to help me with a solution that I believe exists, but that I've not had luck figuring out myself or finding a similar question already answered on a forum.

    In short, I have a report that contains employee absence data where each calendar date absent is shown with a new row. However, I need to use the data to understand how many occurrences someone was out versus number of days (i.e. if an employee is out Tuesday through Thursday, that would be one chargeable absence; however, the report we are able to run shows it as three absences since the absence spans three days). If someone can please suggest a formula I can use to somehow tell Excel to count consecutive absences for each employee as one absence it would be a huge help.

    I inserted my file and I'm inserting a screenshot in hopes that it will make it more clear. I sincerely appreciate your help in advance!!

    For example, in this data set below, I'm hoping a formula can be applied that will show Beth had one absence in April (April 4-6th) and one absence in May (May 2nd).

    Excel Example - Need to count consecutive dates as 1 occurrence.JPG

    Book1.xlsx

    Note: I have it sorted by name currently, but can use employee number as well if that would work better for a formula.

    Thank you again!!
    Attached Files Attached Files
    Last edited by johnson3272; 01-20-2015 at 11:38 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Hi, welcome to the forum

    See if this will get you started...
    =IF(AND(A4=A5,B4+1=B5),"",1)
    copied indo D4 and dcopied down
    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
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    We are to assume weekends don't break the continuity.......Friday followed by a Monday is still one absence?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    hmm good point, FT, didnt think about w/e's

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Quote Originally Posted by FlameRetired View Post
    We are to assume weekends don't break the continuity.......Friday followed by a Monday is still one absence?
    But OP shows Saturday and Sundays in Leaves List.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    How your output would be? pls attach sample excel sheet

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Quote Originally Posted by nflsales View Post
    But OP shows Saturday and Sundays in Leaves List.
    Yes. I notice that now.........hmmm.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    I'll hazard a guess with the attached. I used a helper column J and counted the contiguous periods in column K.

    Columns E, F, G and H were there to help me find some kind of pattern.

    BTW: the file had some dates that were text.

    Edit: Both formulas are array formulas. They must be committed by simultaneously pressing Ctrl + Shift while hitting Enter. Then fill down. You'll know it is entered correctly when you see the {} curly braces around the formula. You don't type these in yourself. Excel does it for you.

    Another BTW: That ugly formula in column J can be replaced by this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It is array entered also.
    Last edited by FlameRetired; 01-21-2015 at 02:00 AM.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    I added a small summary table to this one. This formula can be committed by simply hitting Enter and filling down.

  10. #10
    Registered User
    Join Date
    01-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    5

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Hello everyone - first of all, thanks so much for the replies you've already posted here, so thankful for these! On the note of weekends, good question and so sorry to have not stated earlier, but the employees for which I am running this report are on shift work and can work weekends, so that is why I indicated I was looking to count any consecutive day, regardless of weekend/weekday.

    I am running to meetings for the next few hours and will look into these solutions once I get a break in my schedule today - do appreciate your time!

  11. #11
    Registered User
    Join Date
    01-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    5

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    I wanted to follow up after I tested those formulas from FlameRetired with my massive spreadsheet to report that they worked and this is exactly what we needed! Thank you all SO much for your time and help!!

    Have a lovely weekend!

  12. #12
    Registered User
    Join Date
    01-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    5

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Hello again,

    I've found a small issue I'm running into from the formulas used to count consecutive absences and hoping for a solution I have not been able to figure out over the last few months.

    To use the formulas from FlameRetired's sample attachment above, there are 20 rows in the sample table, thus '20' is used in the formulas multiple times (pasted below). If I dump new rows into the report and the report is now 55 rows long, I found I have to go into the formulas and change '20' to '55' each month. I'm hoping for a way for the formula to just count all rows automatically. Is this possible?

    =IF(SUM(IFERROR(IF(IF($A4=$A4:$A$20,ROW(INDIRECT($C4&":"&MAX($C$4:$C$20))),"")=$C4:$C$20,1,""),""))=1,1,0)
    =SUM(IF($A4=$A$4:$A$20,$F$4:$F$20))

    I really appreciate your help! Please let me know if any questions!

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    At the moment no I can't.

    I've attempted some Dynamic Named Range approaches that have not worked so far.

    I don't think getting the last row from 20 to 55 (without editing each time this changes) will be a problem. It's coordinating that with the relative row addresses of the first rows in each range (when defining these DNRs) that have me stumped at this time. Those also define the lower boundaries in a contiguous calendar array.

    This could take me awhile. Will keep trying.
    Last edited by FlameRetired; 08-04-2015 at 11:47 PM.
    Dave

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    I am considering some alternative approaches to the original formula. Without an example I can't tell if any of them is going to do what you want. If you upload a desensitized example of the 55 row scenario it would be helpful.

  15. #15
    Registered User
    Join Date
    01-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    5

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    So sorry for not including an example. My apologies. Please find attached an idea of the report with 55 rows now. Please let me know if any other questions!

    Book1 (1).xlsx

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Excel "hangs" and then tells me it can't load the file because it's corrupt.

    Edit:

    I remember this one from a few months ago.

    I was eventually able to download the file. I copied and pasted data into the original one with these reworked formulas:
    Array-enter this one in F4 and fill down.

    =IF(COUNTIF($A$4:$A$500,$A4)=1,1,IF(SUM(IFERROR(IF(ROW(INDIRECT($B4&":"&MAX($B4:$B5)))=$B4:$B5,1,0),0))=1,1,0))

    The rest do not need to be array entered.

    In G4 this one.

    =SUMIF($A$4:$A$500,$A4,$F$4:$F4)

    I added a running totals column in column H. No other formulas depend upon it so delete it if you don’t want it. That formula in H4 is this one.

    =SUMIF($A$4:$A4,$A4,$F$4:$F4)

    For the summary table in J:K this one in K4 and filled down.

    =SUMIF($A$4:$A$500,$J4,$F$4:$F$500)

    Chronic need for editing shouldn’t be a problem if the last row is set high enough to accommodate the most data you will anticipate plus a few. I used 500. Avoid the temptation to use whole columns. That puts unnecessary load on your system.

    The file is attached.

    Does this do what you want?
    Last edited by FlameRetired; 08-06-2015 at 05:21 PM.

  17. #17
    Registered User
    Join Date
    02-14-2024
    Location
    England
    MS-Off Ver
    365
    Posts
    1

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Quote Originally Posted by FlameRetired View Post
    We are to assume weekends don't break the continuity.......Friday followed by a Monday is still one absence?
    Hi, I am looking for a very similar solution to this query and your formula works however, it is counting weekends as a break, can you help adjust the formula to discount Sat and Sun as work days, so if the date is consecutive e.g., 15/01/2024 - 24/01/2024 inclusive, that is one occurrence and not two.Test Absence File.xlsx

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: How to count consecutive dates for each employee from a listing of absence dates?

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count of consecutive dates as single occasion per patient
    By mallen91693 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2014, 01:32 AM
  2. [SOLVED] formula to list dates in a table (Listing duplicate dates only once)
    By JRidge in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-11-2013, 03:53 AM
  3. Count Number of Unique Dates For Employee Name
    By Eddiegnz1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2012, 12:20 PM
  4. Replies: 3
    Last Post: 02-16-2012, 01:51 PM
  5. Replies: 0
    Last Post: 05-04-2006, 11:10 AM

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