If not found , set a specific answer instead of 1/0/1900

[Velven]

Hello !


I have a following formula here. I am trying to keep track of the latest transaction made.

This is an Array Formula entered with Control+Shift+Enter.
=MAX(IF($B$1:$B$4=$A8,IF($C$1:$C$4="Cu",$A$1:$A$4)))
=MAX(IF($B$1:$B$4=$A8,IF($C$1:$C$4="Dp",$A$1:$A$4)))
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]Cust1[/TD]
[TD]Dp[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/2/2018[/TD]
[TD]Cust1[/TD]
[TD]Dp[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/5/2018[/TD]
[TD]Cust3
[/TD]
[TD]Cu[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/7/2018[/TD]
[TD]Cust2[/TD]
[TD]Cu[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Expected Answer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Customer[/TD]
[TD]Latest Dp[/TD]
[TD]Latest Cu[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cust1[/TD]
[TD]1/2/2018[/TD]
[TD]Blank[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Cust2[/TD]
[TD]Blank[/TD]
[TD]1/7/2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cust3[/TD]
[TD]Blank[/TD]
[TD]1/5/2018[/TD]
[/TR]
</tbody>[/TABLE]

I would like it to return blank as shown above instead of 1/0/1900 as my cell format is set as date.

How do I do so ?

Thanks a lot once again.


best
Wan

 

[Aladin Akyurek]

Custom format the formula cells as:

[=0]"";General

 

[AlphaFrog]

Cell custom date format

m/d/yyyy;-0;;@

 

[Velven]

Hello

Thanks guys !

Been thinking so much into the formula I didn't think of the custom date format.


Wan