Help with an index match formula involving returning Dates

[willow1985]

I have the below formula that indexes column Q. Column Q has dates in it as well as blanks. This formula is working for me with one exception:

If there are blank cells in Q the formula will return the date: 01/00/1900

How can I modify this formula to return a blank if there is a blank in column Q?

=IFERROR(TEXT(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,FALSE)),"MM/DD/YYYY")&"","")

Any help would be appreciated.

Thank you

Carla

 

[Fluff]

How about
=IFERROR(TEXT(IFERROR(1/(1/INDEX('Manual Log'!Q:Q,MATCH(A2,'Manual Log'!A:A,FALSE))),""),"MM/DD/YYYY")&"","")

 

[jasonb75]

Try

=IFERROR(TEXT(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,FALSE)),"MM/DD/YYYY;;;"),"")

 

[willow1985]

Works perfect, thank you Fluff!

 

[Fluff]

You're welcome & thanks for the feedback

 

[jasonb75]

If Manual Log'!A:A contains only unique entries, then you could also use

=TEXT(MAXIFS(Manual Log'!A:A,A2,Manual Log'!Q:Q),"mm/dd/yyyy;;;")

The semi-colons in the text format eliminate the 01/00/1900 dates and the removal of the match function does away with the need for iferror.

 

[jasonb75]

Too late to edit my previous reply but I just realised that I used the syntax for sumif in error, the formula should be

=TEXT(MAXIFS(Manual Log'!Q:Q,Manual Log'!A:A,A2),"mm/dd/yyyy;;;")

 

[Aladin Akyurek]

=IF(ISNUMBER(V(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,0)))),TEXT(V(),"MM/DD/YYYY"),"")


For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function