CODEWITHSUNDEEP
vbaexceldatetime
GetFree
GetFree

Reputation: 42544

How to write a VBA function whose return value is recognized as a date by Excel?

I wrote the following test function in MS Excel 2002:

Function someDate() As Date
    someDate = Now()
End Function

Then I use the function in a cell, like this =someDate(). But the value is shown as a floating point number.
However, if I do this =now(), the cell value is shown as a date as expected.

How can I write a function that returns a date and Excel automatically recognize the value as a date?

Upvotes: 2

Views: 3027

Answers (3)

user3598756
user3598756

Reputation: 29421

edited to show a possible handling of OS date format

you could leave your function as it is and add this code in ThisWorkbook code pane:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cell As Range
    Dim dateSeparator As String

    dateSeparator = Application.International(xlDateSeparator) '<--| retrieve OS date separator
    For Each cell In Target
        If cell.Formula = "=someDate()" Then
            Select Case Application.International(xlDateOrder) '<--| query OS date format
                Case 0
                    cell.NumberFormat = "mm" & dateSeparator & "dd" & dateSeparator & "yyyy"
                Case 1
                    cell.NumberFormat = "dd" & dateSeparator & "mm" & dateSeparator & "yyyy"
                Case 2
                    cell.NumberFormat = "yyyy" & dateSeparator & "mm" & dateSeparator & "dd"
            End Select
        End If
    Next
End Sub

Upvotes: 2

S Meaden
S Meaden

Reputation: 8270

Not possible AFAIK, just write some code to make Excel interpret that cell as a Date, 

Selection.NumberFormat = "m/d/yyyy"

Though I'm sure you knew that:)

Other ways of forcing a date format are to make the cell the result of a formula where an operand is also a date, so you could have 0 formatted as a date and add to your cell and it would be a date though this is so kludgy that most Excel programmer resile to formatting the cell with some VBA.

Upvotes: 1

TheSilkCode
TheSilkCode

Reputation: 366

Excel stores dates as numbers, so what you are seeing is the date in its numerical format- at the end of whatever you are trying to do, whether that's in a macro or outputting to a cell, just convert back to the date format you prefer at the end using Format for VBA and TEXT for Excel.

Hope this helps, TheSilkCode

Upvotes: 0

Related Questions