Assistance with Array Function for Date Calculation

Hi all,

I've created a spreadsheet full of formulas to work out dates for meetings and when to call for papers, etc. One of the columns is calculated from a rigid set of dates using an array formula, whereas everything else is calculated from the main input date. I need assistance to make the array function work better.

At the moment, I'm using the MAX function with <, it is picking the nearest date before the date in row K. When using MIN and >, it gives me the nearest date after row K. I'm trying to make it so that the date which appears in row D is the nearest date on the SMTDates range regardless of past or future so long as it falls between the dates in rows C and F. I've tried utilising the INDEX function, but have been unable to make that work to any great effect either.

Attach your workbook directly here, please.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

This proposed solution uses two helper columns headed 'Max <' and 'Min >', which may be hidden for aesthetic purposes. The formulas for the two helpers could be incorporated into the formula for column D, but I feel that it is easier to see what they are doing if they are separated.
The array entered formula that populates the 'Max <' column is:

Formula:

=K4-MAX(IF(SMTDates<$K4,SMTDates))

The array entered formula that populates the 'Min >' column is:

Formula:

=ABS(K4-MIN(IF(SMTDates>$K4,SMTDates)))

The array entered formula that populates column D is:

Formula:

=IF(ISBLANK($B4)=FALSE,IF(M4<N4,MAX(IF(SMTDates<$K4,SMTDates)),MIN(IF(SMTDates>$K4,SMTDates))),"")

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.