Logo
×

Excel VBA FormatDateTime Function

VBA FormatDateTime Function: The FormatDateTime function in VBA returns the result as a string after applying a date and/or time format to the supplied expression.

Syntax

FormatDateTime (Expression, [NamedFormat])

Parameter

Expression (specified) – This parameter represents the expression that you want to format.

NamedFormat (optional) – This parameter specifies the format that is to be applied to the Expression. The default value is set to vbGeneral.

It can take the following values:

  • vbGeneral (default)– It displays a date and/or time as defined in your system's General Date setting.
  • vbLongDate- It displays a date as defined in your system's Long Date settings
  • vbLongTime – It displays a time as defined in your system's Long Time settings.
  • vbShortDate – It displays a date as defined in your system's Short Date settings.
  • vbShortTime – It displays a time as defined in your system's Short Time settings.

Return

This function returns a string value after applying a date and/or time format to the supplied expression.

Example 1

Sub FormatDateTimeFunction_Example1()
 'Formating the date in different ways.
 Dim fordat1 As String, fordat2 As String
 fordat1 = FormatDateTime(#10/10/2020#, vbLongTime)
 ' The variable fordat1 will return the String "12:00:00 AM".
 Cells(1, 1).Value = fordat1
 fordat2 = FormatDateTime(#10/10/2020#, vbShortDate)
 ' The variable fordat2 will return the String "10/10/2020".
 Cells(2, 1).Value = fordat2
 End Sub

Output

12:00:00 AM
10/10/2020
VBA FormatDateTime Function

Example 2

Sub FormatDateTimeFunction_Example2()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 fortim1 = FormatDateTime(#12:00:00 PM#, vbLongTime)
 ' The variable fortim1 will return the String "12:00:00 PM".
 Cells(1, 1).Value = fortim1
 fortim2 = FormatDateTime(#12:00:00 PM#, vbShortDate)
 ' The variable fortim2 will return the String "12/30/1899".
 Cells(2, 1).Value = fortim2
 End Sub

Output

12:00:00 PM
12/30/1899
VBA FormatDateTime Function

Example 3

Sub FormatDateTimeFunction_Example3()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 fortim1 = FormatDateTime(#10/10/2020 11:00:00 AM#, vbLongTime)
 ' The variable fortim1 will return the String "11:00:00 AM".
 Cells(1, 1).Value = fortim1
 fortim2 = FormatDateTime(#10/10/2020 11:00:00 AM#, vbShortDate)
 ' The variable fortim2 will return the String "10/10/2020".
 Cells(2, 1).Value = fortim2
 End Sub

Output

11:00:00 AM
10/10/2020
VBA FormatDateTime Function

Example 4

Sub FormatDateTimeFunction_Example4()
 'Formating the date in different ways.
 Dim fortim1 As String, fortim2 As String
 'the exression is not recognised as date
 fortim1 = FormatDateTime("#10/10/2020 11:00:00 AM#", vbLongTime)
 ' The variable fortim1 will return type mismatch run-time error.
 Cells(1, 1).Value = fortim1
 End Sub

Output

VBA FormatDateTime Function

Related Topics

Excel VBA- Pivot Table Fields

VBA- Pivot Table Fields: The Pivot Fields collection contains all the fields from the data source, including any calculated fields. The main aspect of adding a field is its Position...

4 minutes read.

Excel VBA Round Function

VBA Round Function: The Round function in VBA rounds a number to a specified number of decimal places and returns the number. Syntax Round (Number, [NumDigitsAfterDecimal]) Parameter Number (required) –This parameter represents a numeric value one wants...

1 minute read.

Excel VBA Cos Function

VBA Cos Function: The Cos function in VBA returns the cosine value for a supplied angle. Syntax Cos (Number) Parameter Number (required) –This parameter represents the number that you want the absolute value of. Return This function...

1 minute read.

Excel VBA WeekdayName Function

Excel VBA WeekdayName Function: The WeekdayName function in VBA returns a string containing the weekday name, for the specified integer representation of a weekday. Syntax WeekdayName (Weekday, [Abbreviate], [FirstDayOfWeek]) Parameter Weekday (required) – This parameter represents...

2 minutes read.

VBA Color Index Property

What is Color Index Property? The Excel VBA Color Index is used to change the color for the cell or range of cells or text (located under the Font section). It sets the color...

5 minutes read.

Excel VBA String Function

The String function in VBA creates a String, consisting of several repeated characters. Syntax String (Number, Character) Parameter Number (required) – This parameter represents the number of characters in the returned String. Character (required) – This parameter...

1 minute read.

Excel VBA Sgn Function

VBA Sgn Function: The Sgn function in VBA returns an integer (+1, 0, or -1), stating the arithmetic sign for the specified number. Syntax Sgn (Number) Parameter Number (required) –This parameter represents the number that...

1 minute read.

Excel VBA DateSerial Function

The DateSerial function in VBA returns a Date from a supplied year, month, and day number. Syntax DateSerial (Year, Month, Day) Parameter Year (required) – This parameter represents an integer signifying the year. Month (required) – This parameter...

2 minutes read.

CodeIgniter Architecture

Here we will understand the architecture and working of the CodeIgniter application, which helps you to elaborate all steps in simple ways. As the above image represents that whenever a request comes from the...

2 minutes read.

Excel VBA InStr Function

VBA InStr Function The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found...

1 minute read.

Excel VBA TimeValue Function

Excel VBA TimeValue Function: The TimeValue function in VBA returns a Time from the specified String interpretation of a time /date where the date information for the given string is...

1 minute read.

Excel VBA Sin Function

VBA Sin Function: The Sin function in VBA returns the sine value for a supplied angle. Syntax Sin (Number) Parameter Number (required) –This parameter represents the angle (in radians) to calculate the sine value. Return This function...

1 minute read.

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria. Syntax Filter (SourceArray, Match, [Include], [Compare]) Parameter SourceArray (required) – This parameter the array of Strings that you...

2 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

Excel VBA Chr Function

VBA Chr Function: The Chr function in VBA returns the character equivalent to a supplied character code between 0 and 255. Syntax Chr (CharCode) Parameter CharCode (required) – This parameter represents the character code for...

1 minute read.

Excel VBA: Do Until….Loop

DO UNTIL….Loop The “Do Until” Loop is same unlike DO WHILE statement just that it will keep on looping till the condition is not met. This loop is used to repeat a set...

4 minutes read.

VBA Charts Basic Operations

VBA Charts- Basic Operations The Chart Object in Excel VBA represents the collection of all the charts sheet present in a workbook. A chart can be either an embedded chart or a separate chart sheet. The...

6 minutes read.

VBA Subscript out of Range

What is Subscript out of Range? The VBA Subscript out of Range error (which is also called as Run-Time Error 9) mostly triggers when the user selects any cell, sheet, or workbook which does...

5 minutes read.

Excel VBA FormatDateTime Function

VBA FormatDateTime Function: The FormatDateTime function in VBA returns the result as a string after applying a date and/or time format to the supplied expression. Syntax FormatDateTime (Expression, [NamedFormat]) Parameter Expression (specified) – This parameter...

2 minutes read.

VBA Regex Pattern Validation

VBA Regex Pattern Validation The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard...

6 minutes read.