Pick the nearest date from different columns dates after a date in Cell R9

[Stoobydoo]

Hi,
I've dates in column C2 through P2. In Q2 I'd like to look at the dates between C2 and P2 and pick the nearest one after a date in cell R1.

Example,
C2 is 1/2/23
D2 is 5/10/23
P2 is 1/5/23

R1 is 1/4/23 so

Q2 would return P2's 1/5/23

Thanks!

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

As a matter of importance, is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

Many questions (which is why we need a good sample workbook):

1. 1934 or 2034?
2. What is in the columns between these dates?
3. If the dates are not in chronological order, then this is harder, so is 34 a typo???

[Stoobydoo]

Hello, I've added a file I hope. thanks for your help.
34 was a typo but not always will the dates be in order..
I'll edit the attachment such that not all are in order.

[Stoobydoo]

I've amended the attachment.

[AliGW]

There is no file attached - you have to upload, close and then SAVE (Submit).

If the dates will not always be in order, then make sure that the sample reflects this and shows the nature of the data in the columns between the dates columns, otherwise any solution suggested is likely to fail. We cannot be expected to guess what you haven't told/shown us, so make it comprehensive, please.

Which version of Excel are you using?

[AliGW]

No, there is nothing attached.

Which version of Excel are you using?

[Stoobydoo]

It's not giving me an option to Save, It's likely me doing something wrong. I'm also struggling to see which version of excel it is, i'm new to this company. SORRY!

[AliGW]

In the absence of a workbook and no answer to my question about your Excel version, let's start with this:

=MIN(IF(C2:P2>=R2,C2:P2))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

It's NOT going to work, though, if intervening columns contain numbers lower than the serial number of the latest date in the list.

[AliGW]

It's not giving me an option to Save, It's likely me doing something wrong.

1. Browse to the workbook.
2. Click the UPLOAD button.
3. Click to CLOSE the window (top right).
4. Click to SUBMIT the post (bottom right).

For your Excel version, got to File | Account. The details should be there.

[Stoobydoo]

Hopefully this is the attachment.
Your formula returned the earliest date, not the date which is after, but nearest cell R1 (I may have said r2 above).

Version is 2208 (build 15601.20660)

[AliGW]

No, it didn't - see my attachment! Did you enter it correctly? See my instrctions.

Which version of Excel do you have? This is IMPORTANT.

[Stoobydoo]

I uploaded test rev 1. I can see it and when i click it on this forum it opens.

[Stoobydoo]

Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20660) 32-b

[Stoobydoo]

your earlier formula, does work actually, I think. Thanks! I'm checking it.

[AliGW]

Please update your forum profile (instructions above). This is IMPORTANT.

See attached with my formula in place.

[AliGW]

No - not 2208!

MS365 (2208)

Please change it - thanks.

[AliGW]

Thanks for amending the profile.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.