vba format Date() code example

Example 1: excel vba set cell format to date

'In Excel, dates are actually just numbers that have a special
'formatting applied to display like dates.

'VBA code to change the Number Formatting of a cell range:

[A1].NumberFormat = "mm-dd-yyyy"

'There are hundreds of options for Number Formats. The above sets
'cell A1 to display numbers as a date in the American style.

Here are a few other Number Formats:

[A1].NumberFormat = "General"
[A1].NumberFormat = "$#,##0.00"
[A1].NumberFormat = "@"				'<-- Text

Example 2: excel vba date to string conversion

Excel formula to turn a date/time into text:

=TEXT(A1,"DD/MM/YYYY hh:mm:ss AM/PM")

'Reference:
'  https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

'---------------------------------------------------------------------------------------------

'VBA function to do the same thing:
Function DateToText$(dbl#)
    DateToText = Format(dbl, "DD/MM/YYYY hh:mm:ss AM/PM")
End Function