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


---
Message posted from http://www.ExcelForum.com/

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.



In article ,
kvdwerf wrote:

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com