Formula to Check Date Range

1. 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!

2. 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.

3. 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)),"#"),"")

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