Formula to find latest & Old date from a column based on criteria

[Pranesh]

Hi All,

I have 5 columns of data. In which A to D columns are text and E column is date. I need 2 formula in F & G column to check latest and oldest date in column E based on criteria from A to D Column.

For example: In Column E I have dates from 2017 September( In 2017 I have only 2 dates 08/10/2017 & 09/22/2017) to 2019 April(Till date 04/17/2019). Now I need a formula in Column F which should say the latest date from column E which is 04/17/2019 and 1 formula in column G which should say the oldest date from column E which is 09/22/2017.


In this I need this to be based on 3 criteria(Column A,B,C,D). I got a formula from google search which gives the latest date(04/172019) but when I use it to find the oldest date instead of 09/22/2017 it gives output as 08/10/2017

Formula for Latest date: (This works fine)

MAX(IF(B2:B232149="AM",C2:C232149))

Formula for Oldest date: (This didn't work)

MIN(IF(B2:B232149="AM",C2:C232149))

 

[Special-K99]

These are both array formulas but there's no indication you've entered them as such
Reenter them thus
Array formula, use Ctrl-Shift-Enter

 

[Pranesh]

These are both array formulas but there's no indication you've entered them as such
Reenter them thus
Array formula, use Ctrl-Shift-Enter

Hi Special-K99,

Thanks for your assistance. I missed to include that here in thread. In my file i have updated the formula with Ctrl-Shift-Enter. But im not getting the answer im expecting.

 

[Special-K99]

Hang on this doesnt make senese.

"I have 5 columns of data. In which A to D columns are text..."
So column C is text

Then why are you extracting column C ?
MAX(IF(B2:B232149="AM",C2:C232149))
MIN(IF(B2:B232149="AM",C2:C232149))

"...and E column is date"

It should be column E
Even you've said that below.

"I need 2 formula in F & G column to check latest and oldest date in column E based on criteria from A to D Column. "

If this still doesn't explain why you're not getting the right results then supply some example data

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Aladin Akyurek]

[…]

Formula for Oldest date: (This didn't work)

MIN(IF(B2:B232149="AM",C2:C232149))

What do you get?