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 RTrim Function

VBA RTrim Function: The Rtrim function in VBA removes the leading spaces from the text in the specified string. Syntax RTrim (String) Parameter String (required) – This parameter represents the string from which you want...

1 minute read.

VBA Not Equal Operator

What is VBA Not Equal Operator? VBA Not Equal binary operator (“<>”) is a logical function that is used to check if the specified values are not equal or not. This...

5 minutes read.

VBA UBound

VBA UBound  The UBound or Upper Bound function in VBA is used to specify the length of an array and returns the highest subscript for a dimension for the specified array. It is...

3 minutes read.

Excel VBA Right Function

VBA Right Function: The Right function in VBA returns a substring from the end of the given string. Syntax Right (Str, Length) Parameter Str (required) – This parameter represents the string from which you want to...

1 minute read.

Excel VBA Len Function

VBA Len Function: The Len function in VBA returns the number of characters in a supplied string or the number of bytes required to store a supplied variable. Syntax Len (Expression) Parameter Expression (required)-...

1 minute read.

Excel VBA DatePart Function

The DatePart function in VBA returns a part (day, month, week, etc.) for the specified date and/or time. Syntax DatePart (Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear]) Parameter Interval (required) – This parameter represents a string specifying the interval to be used. It can...

2 minutes read.

Excel VBA Replace Function

VBA Replace Function: The Replace function in VBA searches for a substring within the specified string and replaces its occurrences with a second substring. Syntax Replace (Expression, Find, Replace, [Start], [Count], [Compare]) Parameter Expression (required) – This parameter represents...

2 minutes read.

Excel VBA: IF THEN Statement

VBA Excel: IF THEN StatementThis conditional statement enables you to check one condition and on the basis of that then run one or multiple statements if the condition holds. If...

1 minute read.

Excel VBA Year Function

Excel VBA Year Function: The Year function in VBA returns the four-digit year for the specified date. Syntax Year (Date) Parameter Date (required) – This parameter represents the significant date. Return This function returns the four-digit year...

1 minute read.

Introduction to Visual Basic Editor Window

How to enable the Developer Ribbon Tab? In order to work with VBA, users need to make a small change in Excel to display a new tab (Developer) at the top of the...

5 minutes read.

Four VBA Clear methods

Four VBA Clear methods In Microsoft Excel, many times, a situation arises where the user wants to clear the data or any specific range of data. What if the user automates this task with...

6 minutes read.

Excel VBA Mid Function

VBA Mid Function: The Mid function in VBA returns a substring from within a supplied string. Syntax Mid (Str, Start, [Length]) Parameter Str (required) -This parameter represents a string from which you want to extract the substring. Start...

1 minute read.

Excel VBA Now Function

Excel VBA Now Function: The Now function in VBA returns the current date and time.  Syntax Now () Parameter NA Return This function returns the current date and time.  Example 1 Sub NowFunction_Example1() 'It will return...

1 minute 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 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.

Declaring a Variable in VBA

Declaring a Variable A variable is broadly described as a storage location combined with a name and representing a specific value. Declaring a variable is instructing the computer to reserve space...

2 minutes read.

VBA VBScript Regex Methods Regex

VBA VBScript Regex Methods Regex The VBA Regex supports 3 methods which are as follows: ExecuteReplaceText Execute The execute method is used to extract a match from the given based on the defined matching...

3 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 Functions

What is a function? A function is also called a procedure, but it is not a sub procedure, it’s a function procedure. You have already been using some function in Excel,...

7 minutes read.

Excel VBA Weekday Function

Excel VBA Weekday Function: The TimeValue function in VBA returns an integer (1 to 7), signifying the day of the week for the specified date. Syntax Weekday (Date, [FirstDayOfWeek]) Parameter Date (required) – This parameter...

2 minutes read.