VBA FormatDateTime Function

The VBA FormatDateTime function is useful for converting date and time values into different formats. It allows for customization of the display of date and time values, making it easier to present them in a way that is both visually appealing and easily understandable. This function is particularly useful for manipulating data or displaying it in a user-friendly manner in VBA applications.

VBA FormatDateTime Function – Purpose, Syntax and Arguments

Syntax

FormatDateTime(date, [format])

‘date’ and ‘format’. The ‘date’ argument is required and represents the date or time value that needs to be formatted. The ‘format’ argument is optional and represents the specific format that the date or time value should be displayed in. If no ‘format’ argument is provided, the function will return the default short date and time format as determined by the regional settings on the computer.
The FormatDateTime function has two arguments –

Arguments

• date – This represents the date or time value that needs to be formatted. It can be any valid date or time expression, such as a VBA Date, Date and Time, or any string that can be interpreted as a date or time by the computer’s regional settings.
• format – This is an optional argument that specifies the format in which the date or time value should be displayed. It can take on the following values:
  ‘format’ argument is specified.
  • vbGeneralDate – Displays both date and time values in the format determined by the computer’s regional settings. This is the default value if no
  • vbLongDate – Displays the date in the long date format, which includes the weekday, month, and year.
  • vbShortDate – Displays the date in the short date format, which includes only the month, day, and year.
  • vbLongTime – Displays the time in the long time format, which includes hours, minutes, and seconds.
  • vbShortTime – Displays the time in the short time format, which includes only hours and minutes.

Example

Below is an example of how the FormatDateTime function can be used in a VBA macro to format a date value and output it into a message box:

Sub format_date()
Dim date_value As Date
Dim formatted_date As String
'Assign a date value to the variable
date_value = #12/31/2018#
'Format the date value in the long date format
formatted_date = FormatDateTime(date_value, vbLongDate)
'Display the formatted date in a message box
MsgBox "The date is: " & formatted_date
End Sub

In this example, the ‘date_value’ is assigned a date value using the hash symbol before and after the date. This is a common way of declaring date values in VBA. The ‘formatted_date’ variable uses the FormatDateTime function to convert the date value into the long date format. Finally, a message box is displayed with the formatted date value.

Remarks and Important Notes

• It is important to note that the FormatDateTime function can only handle dates within the range of January 1, 100 to December 31, 9999. Dates outside of this range will cause an error. Additionally, the ‘format’ argument is case-sensitive, so it must be entered correctly for the function to work properly.
• Furthermore, the FormatDateTime function is dependent on the regional settings of the computer. This means that the format of the date or time value returned may vary depending on where the code is run. It is always a good practice to specify the desired format when using this function to ensure consistency.
• Lastly, the FormatDateTime function can also be used with the ‘Format’ function to further customize the formatting of the date or time value. This can be useful when working with large datasets or when specific formatting is required for data presentation.

The FormatDateTime function is a useful tool for formatting date and time values in VBA. It provides flexibility and customization options, making it easier to present data in a way that is both visually appealing and easily understandable. By using this function in combination with other VBA functions, developers can create more robust and user-friendly applications.

Understanding VBA FormatDateTime Function with Examples

Simple Format of Date and Time

Sub FormatExample1() 
MsgBox "Today's date and current time is " & FormatDateTime(Now, vbGeneralDate)
End Sub

The FormatDateTime function is used to format and display a date and/or time value according to the specified format in the code. In this example, the Now function is used to retrieve the current date and time and the vbGeneralDate constant is used as the format argument. The resulting message box will display the current date and time in a general format, which includes both the date and time in a long date and time format. The general format can also be written as a shortcut code “0” instead of using the vbGeneralDate constant. Overall, this example demonstrates the use of FormatDateTime function to display the current date and time in a specified format.

Short Date and Long Date Formats

Sub FormatExample2()
MsgBox "Today's date in short date format is " & FormatDateTime(Now, vbShortDate) & vbCrLf & _
"Today's date in long date format is " & FormatDateTime(Now, vbLongDate)
End Sub

In this example, the FormatDateTime function is used to display the current date in both short and long date formats. The vbShortDate constant is used to specify the short date format, which displays the date in a two-digit month, two-digit day, and four-digit year format. The vbLongDate constant is used to specify the long date format, which displays the date in a long month, day, and year format. The vbCrLf code is used to add a line break for better readability in the message box. This example showcases the flexibility of the FormatDateTime function to display date values in different formats.

Short Time and Long Time Formats

Sub FormatExample3()
MsgBox "The current time in short time format is " & FormatDateTime(Now, vbShortTime) & vbCrLf & _
"The current time in long time format is " & FormatDateTime(Now, vbLongTime)
End Sub

Similar to the previous example, this code uses the FormatDateTime function to display the current time in both short and long time formats. The vbShortTime constant is used to specify the short time format, which displays the time in a 12-hour clock format with AM or PM. The vbLongTime constant is used to specify the long time format, which displays the time in a 24-hour clock format. This example demonstrates how the FormatDateTime function can be used to format and display time values in different ways.

Custom Date and Time Formats

Sub FormatExample4()
MsgBox "Today's date in custom format is " & FormatDateTime(Now, "MMMM d, yyyy") & vbCrLf & _
"Current time in custom format is " & FormatDateTime(Now, "h:mm:ss AM/PM")
End Sub

The FormatDateTime function also allows for custom date and time formats to be used. In this example, the code uses different formatting codes to specify a custom format for the date and time. “MMMM” is used for a full month name, “d” for the day without leading zeros, and “yyyy” for the four-digit year. Similarly, “h” represents the hour in a 12-hour clock, “mm” for minutes, and “ss” for seconds. The “AM/PM” code is used to display the AM or PM designation. This example shows how the FormatDateTime function can be used to create personalized date and time formats.

Formatting a Specific Date and Time Value

Sub FormatExample5()
Dim specifiedDate As Date
specifiedDate = #3/20/2020#
MsgBox "The specified date in short date format is " & FormatDateTime(specifiedDate, vbShortDate) & vbCrLf & _
"The specified date in long date format is " & FormatDateTime(specifiedDate, vbLongDate) & vbCrLf & _
"The specified date in custom format is " & FormatDateTime(specifiedDate, "ddddd")
End Sub

In this example, a specific date and time value is assigned to a variable using the pound sign (#) notation. The FormatDateTime function is then used to format and display this value in different formats, including the short and long date formats as well as a custom format using the “ddddd” code, which displays the day of the week. This example demonstrates how the FormatDateTime function can be used to format specific date and time values, not just the current date and time.

Conclusion

The FormatDateTime function is a powerful tool in VBA that allows for the formatting and displaying of date and time values in a variety of formats. It can be used to format both the current date and time as well as specific date and time values. Through the use of various format arguments and custom formats, it provides flexibility in how date and time values are presented. Understanding and utilizing the FormatDateTime function can enhance the user experience in VBA coding and improve the overall functionality of VBA projects.