Date/time formats of the Format function

<< Click to Display Table of Contents >>

Date/time formats of the Format function

Date and time values are simply floating point numbers. The digits to the left of the decimal point define the date, the digits to its right the time. If the number has no digits to the right of the decimal point, it consists of only the date. Conversely, if it has no digits to the left of the decimal point, it consists of only the time.

Date and time values can be formatted using predefined or user-defined formatting codes.

The following table lists the predefined date/time formats for the Format function:

Format name

Description

General Date

Outputs the date and/or time without any formatting (i.e., typically in the short date format).

Short Date

Outputs the date in the short date format.

Medium Date

Outputs the date using month names abbreviated to three characters.

Long Date

Outputs the date in the long date format.

Short Time

Outputs the time in the short time format.

Medium Time

Outputs the time in a 12-hour format (hh:mm AM|PM).

Long Time

Outputs the time in the long time format.

User-defined date and time formats

User-defined formats can be composed of the following format codes.

Important: The format codes are case-sensitive.

Character

Meaning

c

Returns the complete date in the short date format and the complete time in hh:nn:ss format.

d

Returns the day as a number (1-31).

dd

Returns the day as a two-digit number (01-31).

ddd

Returns the weekday abbreviated to three letters (Sun-Sat).

dddd

Returns the weekday (Sunday-Saturday).

ddddd

Returns the full date in the short date format.

dddddd

Returns the full date in the long date format.

w

Returns the weekday as a number (1-7), 1=Sunday, 2=Monday, ... 7=Saturday.

 

 

m

Returns the month as a number (1-12).

mm

Returns the month as a two-digit number (01-12).

mmm

Returns the month name abbreviated to three letters (Jan-Dec).

mmmm

Returns the month name (January-December).

q

Returns the quarter as a number (1-4).

yy

Returns the year as a two-digit number (00-99).

yyyy

Returns the year as a three- to four-digit number (100-9999).

h

Returns the hours as a number (0-23).

hh

Returns the hours as a two-digit number (00-23).

n

Returns the minutes as a number (0-59).

nn

Returns the minutes as a two-digit number (00-59).

s

Returns the seconds as a number (0-59).

ss

Returns the seconds as a two-digit number (00-59).

 

 

AM/PM

Use 12-hour format and display AM or PM

am/pm

Use 12-hour format and display am or pm

A/P

Use 12-hour format and display A or P

a/p

Use 12-hour format and display a or p

Examples

Some examples are shown in the following table:

Format

Result for February 26, 2020 at 18:45:15

"m/d/yy"

2/26/20

"mmm d, yyyy"

Feb 26, 2020

"hh:nn AM/PM"

06:45 PM

"hh:nn:ss"

18:45:15