Function return a Date, wrong format

  • Thread starter Thread starter kvdwerf
  • Start date Start date
K

kvdwerf

Excel 2000 problem:


When putting in a cell a formula referring to a custom VBA function,
that returns a date or variant/date, the result is formatted as a
number.

When putting in a cell a std function like =TODAY(), the result is
formatted as a date.

Questions: does this problem occur in higher versions of Excel as well,
and is there maybe some way around?

I already tried to return a Variant/String containing the preformatted
date, but Excel does not recognize result as a date




'Results in the number "37987" appearing in the cell
Public Function TestDateFormat1() As Date
TestDateFormat1 = Date 'Returns the current system date
End Function

'Results in the number "37987" appearing in the cell
Public Function TestDateFormat2() As Variant
TestDateFormat2 = Date
End Function 'Results in 37987 in the cell


'Results in a left aligned string "2004/01/01" in the cell
Public Function TestDateFormat3() As Variant
TestDateFormat3 = Format(Date, "yyyy/mm/dd")
End Function


Many thanks
 
When VBA returns a value to XL, it is parsed much like it would be
if you typed the result in by hand - the format displayed will be
the format of the cell, not of the entry.

Instead of formatting the result, just format the cell.
 
Thanks.
But this definitely is not sufficient. Try this in a cell

=TestNestedDateFunction(TestDateFormat1()),

with


Public Function TestNestedDateFunction(value As Variant) As String
TestNestedDateFunction = IIf(IsDate(value), "IsDate", "No
IsDate")
End Function


This results in a "Not IsDate", except for the third variant
TestDateFormat3.
And I really need an IsDate outcome, as well as a Date-value resultin
from a TestDateFormatX-cal
 
Back
Top