+ Reply to Thread
Results 1 to 2 of 2

Date Calculation based on criteria

  1. #1
    Registered User
    Join Date
    06-26-2019
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    26

    Date Calculation based on criteria

    Hi All,

    I have a challenge, which I have only partially resolved and need some assistance.

    I need the solution to work on Excel 2007 and all subsequent version. not just office 365

    I have
    Column D - Arrival Date
    Column E - Departure Date
    Column F - Days (Calculation)

    In Field K3 - the Initial Arrival Date
    Field L3 - Maximum Time Period (End Date)
    Field M3 - Calculation part 1

    The aim is to be able to calculate have many days someone has spent between the period L3-L3 Inclusive.

    The Calculation in M3 is working for any row where both the Arrival Date and End Date are in the range specified in K3 / L3.
    I am struggling with the necessary formula for calculating the net days that only fit within the Range K3 / L3. So Row 6 only 3 days should be calculated and included in the final answer.

    Cell H3 shows the calculation I am trying to include for the date ranges which exist outside the criteria or something similar - so The answer I would like the formula to give me back as an answer is in H8.

    I have done the basic formulas I am looking at in column I.

    I need a formula that sums 3 different situations:
    1) Arrival Date Before K3 and Departure Date Less than = L3.
    2) Arrival Date Greater than = K3 and Departure Date Less than = L3.
    3) Arrival Date greater than = K3 and Departure Date greater than L3.

    Hope this is clear.
    Many thanks
    Attached Files Attached Files
    Last edited by green_aardvard; 07-21-2021 at 10:51 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Date Calculation based on criteria

    for backwards capability, something like below -- note Array:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    however, I've assumed that first row should return 181 rather than 180 unless the initial/end dates are exclusive of the range.

+ 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: 3
    Last Post: 09-24-2019, 04:29 PM
  2. [SOLVED] Calculation Based on Date Criteria
    By Mgc26133 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2018, 10:53 AM
  3. [SOLVED] Percentage calculation based on multiple date criteria
    By odahilys in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-26-2015, 10:10 AM
  4. Replies: 4
    Last Post: 06-02-2012, 11:26 AM
  5. Calculation based on a criteria
    By hoosier5 in forum Excel General
    Replies: 3
    Last Post: 10-25-2011, 10:40 AM
  6. Criteria based calculation help.
    By rosarionyc in forum Excel General
    Replies: 18
    Last Post: 11-19-2010, 05:49 PM
  7. [SOLVED] Date Calculation According to the Given Criteria
    By earthworm in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-19-2009, 02:46 AM

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