Worksheet functions cannot change the formatting of the calling cell (or any
other cell).
They can only return a value.

You could return a formatted string, but then any other formula that
referenced the result would not work unless it converted the string back to
a date

regards
Charles

"graham_s" wrote in message
...
I want to format the cell that contains a function. The function is passed
the contents of anothe cell and removes an "A" or "*" as required. I want
to
format the cell containing the function to be date format. This can
obviously
be done manually but I would like to do it automatically. The format
command
below has no effect.

How can I do this?
Can I pass or somehow obtain the cell ref of the cell containing the
function?



Public Function fncExtractDate(str As String) As Date

'22/04/07 A
'22/04/07 *
'22/04/2009

Dim d As Date

If InStr(str, "A") = 0 And InStr(str, "*") = 0 Then
'valid date
d = CDate(str)
Else
'have "A" or "*". read the first 8 characters
d = CDate(Left(str, 8))
End If

fncExtractDate = Format(d, "dd/mm/yyyy")

End Function



TIA

Graham_s