+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Subtract a date range on, around or within another date range

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Cool Subtract a date range on, around or within another date range

    Hi, so I need a forumla that will calculate the number of days from one date range that are within another date range.

    I have four quarters:

    April 1, 2012 - June 30, 2012
    July 1, 2012 - Sept 30, 2012
    Oct 1, 2012 - Dec 31, 2012
    Jan 1, 2013 - Mar 31, 2013

    I need to know if someone took sick time (or any kind of leave) how many days they took from EACH quarter.
    ie. Sick time = April 15, 2012 - July 15, 2012
    =92 days in quarter 1
    =15 days in quarter 2
    but I would also like excel to recognize if the leave time is within the quarter range, cause if not there is no need for an equation
    I will have 4 separate cells that calculate each quarters results

    Note: networkdays will not work, as I need an exact count of days including weekends

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,185

    Re: Subtract a date range on, around or within another date range

    Hi tiggynook, Welcome to the forum.

    Can you please attach a sample file? So the members can try based on your data layout. If you have any confidential information, replace them with dummy.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Subtract a date range on, around or within another date range

    Ok so I have attached a sample spreadhseet I am working on. I need help with the PINK section to the right, the numbers in RED are the CORRECT numbers I need to achieve with a formula.

    I have entered 4 examples, I need the forumla to determine WHERE or IF the LEAVE DATES fall into the corresponding QUARTER DATES, then calculate how many DAYS were absent from EACH QUARTER.

    If you need anymore information please let me know.

    Thank You!!!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,185

    Re: Subtract a date range on, around or within another date range

    In Z5 is not be 32 instead of 62? See the attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Subtract a date range on, around or within another date range

    Yes Z5 is supposed to be 32. Thank You

    And thank you sooooo much for the formula, I just couldn't figure this one out on my own!
    Thank You
    Thank You
    Thank You!!!

  6. #6
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Subtract a date range on, around or within another date range

    I think i spoke too soon,
    When continuing to enter information into the sheet I realized that if I enter someones information who DOES NOT take any leave time it is marking the amount of days absent as the total quarter.
    I need this forumal to come back with a "0" if no leave time is taken, since I am only tracking absent days in thsi column.

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,185

    Re: Subtract a date range on, around or within another date range

    Use these formulas,

    Y4,

    Please Login or Register  to view this content.
    Z4,

    Please Login or Register  to view this content.
    AA4,

    Please Login or Register  to view this content.
    AB4,

    Please Login or Register  to view this content.
    Then copy Y4:AB4 & paste down as needed.

  8. #8
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Subtract a date range on, around or within another date range

    THANK YOU!!!!

    works perfectly now

  9. #9
    Registered User
    Join Date
    05-09-2012
    Location
    Thunder Bay, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Subtract a date range on, around or within another date range

    So I had marked this as solved because everything was working great for a while. Now I have a new scenario and the forumla is not calculating it properly.

    If one of the quarters is left blank (no dates input), and a set of leave dates is input, it is not working. I have attached an example of the error in cell AJ 172.
    Quarter 1 (Peach section) was left blank becuase this particular person does not have a start date until the next quarter (blue section). The forumla I guess is referencing the blank cells to try and calculate how many days are absent from that quarter base on the leave dates (Pink section). I need the formula to come back with a "0" if any quarter has no dates in them.

    I would like the forumla to recognize this for all rows, not just row 172 just in case this scenario happens again, as the information is always changing.

    Let me know if this makes sense, or if you need more information.
    Attached Files Attached Files
    Last edited by tiggynook; 06-21-2012 at 11:18 AM. Reason: Wrong attachment

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,185

    Re: Subtract a date range on, around or within another date range

    Aj4: =max(0,min(j4,ai4)-max(i4,ah4)+1)*(count(ah4,ai4,i4,j4)=4)
    ak4: =max(0,min(p4,ai4)-max(o4,ah4)+1)*(count(ah4,ai4,p4,o4)=4)
    al4: =max(0,min(v4,ai4)-max(u4,ah4)+1)*(count(ah4,ai4,v4,u4)=4)
    am4: =max(0,min(ab4,ai4)-max(aa4,ah4)+1)*(count(ah4,ai4,ab4,aa4)=4)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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