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)))

	A	B	C
1	1/1/2018	Cust1	Dp
2	1/2/2018	Cust1	Dp
3	1/5/2018	Cust3	Cu
4	1/7/2018	Cust2	Cu
5
6	Expected Answer
7	Customer	Latest Dp	Latest Cu
8	Cust1	1/2/2018	Blank
9	Cust2	Blank	1/7/2018
10	Cust3	Blank	1/5/2018

<tbody>
</tbody>

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