Can we combine DateValue and Aggregate function?

[Khalil Lakhani]

Hello Everyone!
I have a date column, from which I need to pick the earliest date by using AGGREGATE function. But it doesnt work with the data format. So, I have to convert that date using DATEVALUE and then use aggregate function to get the desired result. Can I use both functions at the same time?

Thanks in advance!!

 

[SirJB7]

Hi, Khalil Lakhani!
Dates in column I are text, even the custom format is specified as mm/dd/yyyy (that only applies to numeric/date data).
So you have to either press F2 in each cell and then Enter, hence transforming from text to date, and then use whatever function you want, or you have to use a helper column with Datevalue.
Regards!

 

[bosco_yip]

Try to Change Column I data from Text value to Numeric value by :

Select I2:I15 >> Data >> Text to Columns... >> Next >> Next >> in step 3 of 3, click "Date" and choose "YMD" from the dropdown list >> Finish.

Then, the AGGREGATE formula in K2 will work

Regards
Bosco

 

[shrivallabha]

Another possible workaround would be Array entered (CTRL+SHIFT+ENTER):
=MIN(IFERROR(DATEVALUE(I2:I15),99999))

 

[NARAYANK991]

Hi ,

You do not need to combine the DATEVALUE function with the AGGREGATE function in this case.

Go through this link :

http://www.exceluser.com/excel_help/functions/function-aggregate.htm

The following formula will work :

=AGGREGATE(15,6,I2:I15+0,1)

Narayan