Formula to find the earliest date in excel base on a given date and text

**Rikii Sumat** · 10-26-2017, 04:38 AM

Hello,

I would like to ask if anyone can help me figure out the formula to easily capture the necessary information.

This is what im trying to accomplish, in the attached file i would like to get the earliest date "payment run date (column E)" base on the contract end date (column D) considering the payment run date for US and CAN (column H and I).

i was able to get the earliest date using =MIN(IF(I:I>D2,I:I)) , but having a hard time adding criteria to separate the payment run date for US and CAN data.

In short, i want a formula to get the earliest payment run considering the contract end date while also considering the schedule of payment run per country.

line #9 CAN should get 02/02/17 and not the 01/02/2017 which is for US.

Bare with my grammar folks.

Thank you
Rikii

**leelnich** · 10-26-2017, 04:47 AM

Hi Rikii, welcome to the forum! This ARRAY FORMULA* is just a straight-forward extension of your approach:

Please Login or Register  to view this content.

While you can't use the AND function with arrays, you CAN multiply the conditional clauses to yield an array of 1s (TRUE) and 0s (FALSE). This is equivalent to ANDing them. NOTE: I highly recommend staying away from full-column references. They really slow down calculation.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

**Rikii Sumat** · 10-26-2017, 05:08 AM

Thank you leelnich, unfortunately its not working, see below. the result is 0..

Country Task ID Client Reference Contract End Date Payment Run
US 30827401 7530516561 01/30/17 0
US 30478145 6917620582 11/30/17 0
US 30651906 8250048543 09/30/17 0
US 30827405 7530516544 11/30/17 0
US 30729542 7987708586 11/30/17 0
US 30710572 7498620338 11/30/17 0
US 30729543 7987708659 11/30/17 0
CAN 30827408 7530516192 01/30/17 0
CAN 30817019 7858194911 11/30/17 0

**AliGW** · 10-26-2017, 05:08 AM

Attach your workbook.

**Rikii Sumat** · 10-26-2017, 05:12 AM

already did. thanks

**Rikii Sumat** · 10-26-2017, 05:15 AM

If anyone has much better formula please show me, i would really appreciate the help. thank you

**AliGW** · 10-26-2017, 05:22 AM

Please attach the version of the workbook showing that the suggestion in post #2 does not work for you.

Also, please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

**leelnich** · 10-26-2017, 05:27 AM

It's an array formula, just as your original was. You must press CTRL+SHIFT+ENTER after pasting or editing in the formula bar to confirm. You should see curly brackets {} embrace the text, signifying successful array entry. Then copy down; you'll get the correct output.

**shukla.ankur281190** · 10-26-2017, 05:30 AM

Lee has provided correct formula you would need to press control+shift+enter as it is an array formula.

or can try non array formula

E2

Formula:

Please Login or Register  to view this content.

**leelnich** · 10-26-2017, 05:55 AM

Hi Ankur, haven't heard from you in a while. Here's another non-array alternative:

Please Login or Register  to view this content.

The AGGREGATE() function can mimic other functions - SMALL() in this case - and ignore errors within the input array! So I divided each possible value by each test clause, returning 1 (TRUE) or 0 (FALSE) as divisors. VALUE/1 = VALUE , while VALUE/0 causes a #DIV/0! error, which is duly ignored by the function. Once all rows are processed, it returns the smallest "surviving" number.

**Rikii Sumat** · 10-26-2017, 08:13 PM

hi guys, i would like to apologies if i sounded demanding i didn't mean to.

all these formulas are actually working, cant believe i wasted too much time figuring this by myself.

You guys are awesome. you just save my life (and career) ...lol

Special thanks to leelnich

**Rikii Sumat** · 10-27-2017, 02:01 AM

Hi guys, its me again, sorry to bother you all.

i have one last question though, is there a similar formula that i can use that is much faster to calculate?
i mean in just a week or two, my excel will end up with 2000 or more lines.

The formula is working, though when i tried to plot it it freezing my worksheet.

Thank you

**shukla.ankur281190** · 10-27-2017, 02:13 AM

Try with dynamic range

Formula:

Please Login or Register  to view this content.

**Rikii Sumat** · 10-27-2017, 02:35 AM

All of these formulas are working. I'll test which one is the faster to calculate.

Thanks

Formula to find the earliest date in excel base on a given date and text

LinkBack

Thread Tools

Rate This Thread

Display

Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Re: Formula to find the earliest date in excel base on a given date and text

Thread Information

Users Browsing this Thread

Similar Threads

How to find Earliest Date

[SOLVED] Formula to subtract from 2 date cells and use the earliest date

Find earliest date given a >= threshold

Excel find earliest date within 12 month period.

[SOLVED] Formula that displays the oldest date and earliest date

Excel 2007 : Find the earliest date in a column when there is also text in the column

Find out the earliest date

Bookmarks

Bookmarks

Posting Permissions