Find MAX value in a UNIQUE(FILTER column

[ollyhughes1982]

Hi,

I have a UNIQUE(FILTER list of date values in a column (B), and was wondering how to find the latest date in this column? See images, below:

In a normally populated column, I would just use something like below:

=MAX(B4:B2003)
or
=MAX('All Completed Runs - GHD'!B4:B2003) if referencing from another worksheet

However, this doesn't work and gives me '01/01/1900'. I am expecting the result '13/05/2017', as that's the latest date in the list. Is there a way to use the MAX function on a column that's being populated by a UNIQUE(FILTER function?

Thanks in advance!

Olly.

 

[Fluff]

How about

=MAX(B4#)

 

[ollyhughes1982]

How about

=MAX(B4#)

Hi. Yes, I tried that, but still get '00/01/1900'. I thought that would work as well, but still get the same result.

 

[Fluff]

That would suggest that those dates are text & not real dates.

 

[ollyhughes1982]

That would suggest that those dates are text & not real dates.

I did think that as well, but it's definitely formatted as a date column. Not sure what's going on.

 

[Fluff]

Change the format to general & what do you see?

 

[ollyhughes1982]

Change the format to general & what do you see?

No change. It's really strange

 

[Fluff]

In that case they are text, you will need to convert them.

 

[ollyhughes1982]

In that case they are text, you will need to convert them.

The column where the filter is is text? It shows it set correctly though. Or the field in the source worksheet? I've checked the source and that's also set as date.

 

[Fluff]

The cell format is irrelevant, it does not change the underlying value.
You need to convert those values into dates at the source.