+ Reply to Thread
Results 1 to 3 of 3

Count days between two dates that fall between two other dates.

  1. 12-09-2019, 01:02 AM #1
    Cich
    Cich is offline
    Registered User
    Join Date
    03-12-2016
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    2

    Count days between two dates that fall between two other dates.

    Hi,
    I have to count the days between two dates, which I can do. But I need to count only the days that fall between those dates but only if they fall between another pay period date range.

    Example in first row "Employee 1" if I have a staff member on leave from 25/11/19 till 06/12/19 but I need to count how many days between that range but only if they fall between pay period 02/12/19 - 08/12/19 (Inclusive) I would expect a result of 5 days with the correct formula. It should count 2nd, 3rd, 4th, 5th and 6th as days.

    I have tried lots of different formulas and just cant get the result I need

    Can anyone help please? Example attached spreadsheet with wrong formula.

    I just know if this is actually possible??

    Thanks in advance
    Attached Files Attached Files
    Last edited by Cich; 12-09-2019 at 01:07 AM.
    Reply With QuoteRegister To Reply
  2. 12-09-2019, 01:38 AM #2
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,783

    Re: Count days between two dates that fall between two other dates.

    B2=SUMPRODUCT(((ROW(INDIRECT($G$1&":"&$G$2)))>=INT($C2))*(((ROW(INDIRECT($G$1&":"&$G$2)))<=INT($D2))))

    copy down
    Reply With QuoteRegister To Reply
  3. 12-09-2019, 03:17 AM #3
    Cich
    Cich is offline
    Registered User
    Join Date
    03-12-2016
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    2

    Re: Count days between two dates that fall between two other dates.

    That works great, thanks so much I would never have come up with that. It works better than I expected.
    My next issue was some employee names appear more than once but your formula meant that wasn't an issue either.
    I am so very appreciative, thanks again
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how would I count dates (not # of days) in cells that fall betwee.
    By Max in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 PM
  2. how would I count dates (not # of days) in cells that fall betwee.
    By sailingscotts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  3. how would I count dates (not # of days) in cells that fall betwee.
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 09:05 AM
  4. [SOLVED] how would I count dates (not # of days) in cells that fall betwee.
    By Max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] how would I count dates (not # of days) in cells that fall betwee.
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  6. how would I count dates (not # of days) in cells that fall betwee.
    By sailingscotts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  7. how would I count dates (not # of days) in cells that fall betwee.
    By sailingscotts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] how would I count dates (not # of days) in cells that fall betwee.
    By sailingscotts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1