HomeVBA Functions LIST (Category Wise)How to use the VBA FORMATDATETIME Function (Syntax + Example)


The VBA FORMATDATETIME function is listed under the data type conversion category of VBA functions. When you use it in a VBA code, it returns a string by applying date and time format to it and returns it as a string. In simple words, when you apply an expression it can apply data and time format to it.

how to use it

To use VBA's FORMATDATETIME function you need to understand its syntax and arguments:


FormatDateTime(Expression, [NamedFormat])


  • Expression: The value that you want to format as a date.
  • [NamedFormat]: An vbDateTimeFormat enumeration to specify the format that you want to apply to the Expression.
    • vbGeneral: As per the system's general date setting.
    • vbLongDate: As per the system's Long Date settings.
    • vbLongTime: As per the system's Long Time settings.
    • vbShortDate: As per the system's Short Date settings.
    • vbShortTime: As per system's Short Time settings.

Example to use FORMATDATETIME Function in VBA

To practically understand how to use VBA FORMATDATETIME function, you need to go through the below example where we have written a vba code by using it:

Range("B1").Value = FormatDateTime(Range("A1"))
End Sub

In the above code, we have used the FORMATDATETIME to convert the value in the cell A1 into a valid date and time value (as per VBA) and returned the result in cell B1.


Below are some important points which you need to take care while using FORMATDATETIME function in VBA.

  • If the value specified is a value other than a date and time or a string that can't be recognized as a date and time, VBA will return the run-time 13 error.

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.