Generating Max date from cells with formulas

[Corleone]

Hi
This would be straightforward enough to fix by setting up a recorded macro however the company im doing this for has a block on macros so im tring to avoid them

I import a load of dates from a project plan into the sheet into columns C&D - some of the activity dates have been completed and so they come across in this format 12/11/2022 A
this isnt a problem as i can use the following formula =TEXT(LEFT(d4,9),"DD-MMM-YY") In order to strip out any other characters apart from thE date so this is what i have done in column E&F

MY Problem is In Column G in need to put in a formula which will pick out the latest date from columns E&F - I have tried MAX and MAXA but it doesnt seem to work as the entries in column E&F are formulas.
so in the example below i want 08-nov-19 returned in the first red cell and 18-dec-19 returned in the cell below.

Any ideas welcome

 

[Sufiyan97]

Try

Book5
	D	E	F	G	H
1
2			11/8/2019
3				11/8/2019
4		12/18/2019		12/18/2019
5
6
7
8
Sheet1

Cell Formulas
Range	Formula
G3:G4	G3	=AGGREGATE(15,6,$E$2:$F$6,ROWS($G$3:G3))

 

[Corleone]

=AGGREGATE(15,6,$E$2:$F$6,ROWS($G$3:G3))

I just get this on that formula

 

[Sufiyan97]

Are your dates are real dates or text formatted,

What does =ISNUMBER(F3) returns? TRUE or FALSE


if it return FALSE then you need to use your TEXT formula like:

=--TEXT(LEFT(F2,9),"DD-MMM-YY")

 

[Fluff]

it doesnt seem to work as the entries in column E&F are formulas.

It's not because they are formula, it's because the formula returns text & not a date. Change the formula to

=LEFT(D4,9)+0

and then you will have a real date.

 

[Corleone]

It's not because they are formula, it's because the formula returns text & not a date. Change the formula to

=LEFT(D4,9)+0

and then you will have a real date.

This looks like the most elegant solution and works perfectly for the cells where there are dates - however , is there a way of modifying the formula so that if the cell it is looking up is blank it just returns a blank or 0 at the moment it returns #Value

Thanks all for your responses

 

[Fluff]

How about

=IF(D4="",0,LEFT(D4,9)+0)

 

[Corleone]

How about

=IF(D4="",0,LEFT(D4,9)+0)

Once again thank you for your help in solving the issue

 

[Fluff]

Glad we could help & thanks for the feedback.