+ Reply to Thread
Results 1 to 3 of 3

Formula to Check Date Range

  1. #1
    Registered User
    Join Date
    01-11-2021
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2013
    Posts
    2

    Formula to Check Date Range

    Greetings all,

    I'm looking for a faster formula to accomplish what is accomplished in the range J4:Z8 of the attached spreadsheet - it checks whether the above month falls within a range of dates in the area C4:H8, and then assigns the cell a value based on an IF formula.

    The actual sheet I'm working in includes 200 components, each part of 7 distinct phases that occur over a period of 4 years, split up by week. Thus, the actual function is a series of 7 nested IF functions, and the formula itself is present in over 90,000 cells - as you can imagine, the computing process is quite slow, and any change made to the other sheet columns results in a long wait while each of these calendar rows is recalculated. I'm looking for something that more quickly checks where the date in Row 3 falls with respect to columns C:H, and returns a value based on that check.

    Thanks!
    Attached Files Attached Files
    Last edited by Stadler; 01-21-2021 at 12:57 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Formula to Check Date Range

    Hi there,

    If the dates from one phase to the next are consequtive, you may could simplify the data/formula by working backwards.
    There are still nested 'if' formulas, but you can eliminate the 'and' arguments.

    In the attached file, your original is at the bottom, my option at the top.
    If you can't remove the phase end dates, adjust the formulas for every second column. A bit more work to setup as you can't just pull across.

    Not sure how much it will speed up your process.
    Attached Files Attached Files
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Formula to Check Date Range

    As the dates in each row are in ascending order, In J4 then copied across.
    This is much faster than previous formula.

    =IFERROR(TEXT(INDEX($C$3:$H$3,MATCH(J$3,$C4:$H4,1)),"#"),"")
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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: 7
    Last Post: 07-14-2017, 12:04 PM
  2. Hotel Check in and Check out range date report
    By sharky123 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-30-2016, 11:19 AM
  3. [SOLVED] Need help creating a formula to check a date range
    By bbeards2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-20-2015, 11:43 AM
  4. Check a date range and return value based on the date range
    By KeithCar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2015, 12:55 PM
  5. [SOLVED] Formula to check date range and then do calculation
    By ps65 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2014, 07:54 PM
  6. excel formula help to get date from range as per check criteria
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2013, 06:03 AM
  7. conditional formatting formula to check frequency of occurence in date range?
    By seanblanton in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-27-2013, 05:50 PM

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