'shg' has answered your question, but I just wanted to point out you can
simplify your function as follows...

Public Function YESTERDAY() As Date
YESTERDAY = Date - 1
End Function

This is slightly different than your function in that it does not return the
time. If you wanted the time returned, just use Now instead of Date.

--
Rick (MVP - Excel)


"Jan Kuèera" wrote in message
...
Hi all,
if I enter =TODAY() into a cell, its format changes into Date. How could
my own UDF do the same?

I've tried
Public Function YESTERDAY() As Date
YESTERDAY = DateAdd("d", -1, Now)
Application.ThisCell.NumberFormat = "m/d/yyyy"
End Function

But setting the NumberFormat property inside UDF has no effect.
Any way here?

Thanks,
Jan