CODEWITHSUNDEEP
javascriptvbaexceldate
QQ Lam
QQ Lam

Reputation: 33

Need help to output vba excel function as date value

this is my function:

function foo() as date
   foo = "08/15/2015"
end function

is return 42231 instead of "8/15/2015" if I change the function to below code: 

function foo()
   foo = format("08/15/2015", "dd/mm/yyyy")
end function

then it works, but I am unable to format the output to any other type such as longdate or short date, or manually change it to "dd/mm/yyyy". Do we have any solution that a vba function can output a date value, in the date format, and the output can be formatted to another type manually by user? Example: I have the ouput "8/15/2015", and I can manually format it to "Aug/15/2015"

Upvotes: 1

Views: 501

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

A UDF can only return a value to a cell, not format that cell. So:

Public Function foo() As Date
    foo = DateSerial(2015, 8, 15)
End Function

will display:

enter image description here

only if you format it that way.

NOTE:

This is true of UDFs. If you manually enter:

=TODAY()

in a cell, Excel will format the cell to a date format by itself.

Upvotes: 3

Related Questions