Date Formula Query

[Quinton]

Hello I have a range of dates and basically want to show the latest date:

Example data as below:

01/01/09
01/09/09
01/01/09
01/09/11
01/08/11

Result would be 01/08/11

Many thanks

 

[VoG]

If you mean the last date in a list

Excel Workbook
	A	B
1	01/01/2009	01/08/2011
2	01/09/2009
3	01/01/2009
4	01/09/2011
5	01/08/2011
Sheet1

http://www.excel-jeanie-html.de/index.php?f=1

 

[Quinton]

Many thanks. I put the following formula in:

=LOOKUP(9000000000+308,B4:B17)

However it gave me the Earliest date and not the last date?

Have I done something wrong?

Many thanks again

 

[Quinton]

Whoops just realised that my 1st post was wrong.

The result date should have been 01/09/11 - If this helps

 

[VoG]

That formula was a typo which I corrected. But what you need is

Excel Workbook
	A	B
1	01/01/2009	01/09/2011
2	01/09/2009
3	01/01/2009
4	01/09/2011
5	01/08/2011
Sheet1

 

[Quinton]

Perfect mant thanks (ps how easy sorry!).

Sorry but I have 1 further problem - as follows):

Within the range of dates I have some fixed dates of 20-02-20. I would like the formula to exclude these dates. So the formula would be like this:

=MAX(G20:G31) - but to also exclude any date that is 20-02-20.

Many thanks again in advance!

 

[barry houdini]

You can exclude those with an "array formula" e.g.

=MAX(IF(G20:G31<>DATE(2020,2,20),G20:G31))

formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

 

[VoG]

Try this array formula confirmed with CTRL + Shift + Enter

=MAX(IF(G20:G32<>DATE(2020,2,20),G20:G32))