Format Property - Date/Time Data Type

You can set the Format property to predefined date and time formats or use custom formats for the Date/Time data type.

Setting

The following sections illustrate predefined and custom formats.

Predefined Formats

The following table shows the predefined Format property settings for the Date/Time data type.

Format

Description

Example

General Date

(Default) Displays date values as numbers and time values as hours, minutes, and seconds followed by AM or PM. For both types of values, Access uses the date and time separators specified in your Windows regional settings. If the value does not have a time component, Access displays only the date. If the value has no date component, Access displays only the time.

06/30/2018 10:10:42 AM

Long Date

Displays only date values, as specified by the Long Date format in your Windows regional settings.

Monday, August 27, 2018

Medium Date

Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings.

27/Aug/18
27-Aug-18

Short Date

Displays date values, as specified by the Short Date format in your Windows regional settings.

8/27/2018
8-27-2018

Long Time

Displays hours, minutes, and seconds followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10:42 AM

Medium Time

Displays hours and minutes followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10 AM

Short Time

Displays only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings.

10:10

Custom Formats

To create a custom format, use the following characters as placeholders and separators. Custom formats that are inconsistent with the date/time settings specified in Windows regional settings are ignored. For more information, see Change the Windows regional settings to modify the appearance of some data types.

Character

Description

Date separator

Controls where Access places the separator for days, months, and years. Use the separator defined in the Windows regional settings. For example, in English (U.S.), use a slash (/).

Time separator

Controls where Access places separators for hours, minutes, and seconds. Use the separator defined in the Windows regional settings. For example, in English (U.S.), use a colon (:) .

c

Displays the general date format.

d or dd

Displays the day of the month as one or two digits. For one digit, use a single placeholder; for two digits, use two placeholders.

ddd

Abbreviates the day of the week to three-letters.

dddd

Spells out all days of the week.

ddddd

Displays the Short Date format.

dddddd

Displays the Long Date format.

w

Displays a number that corresponds to the day of the week. (1 to 7)

ww

Displays a number that corresponds to the week of the year (1 to 53).

m or mm

Displays the month as either a one-digit or two-digit number.

mmm

Abbreviates the name of the month to three-letters. For example, January appears as Jan.

mmmm

Spells out all month names.

q

Displays the number of the current calendar quarter (1-4). For example, if you hire a worker in May, Access will display 2 as the quarter value.

y

Displays the day of the year, 1-366.

yy

Displays the last two digits of the year.

Note: We recommend entering and display all four digits of a given year.

yyyy

Displays all digits in a year in the range 0100-9999.

h or hh

Displays the hour as one or two digits.

n or nn

Displays minutes as one or two digits.

s or ss

Displays seconds as one or two digits.

tttt

Displays the Long Time format.

AM/PM

Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate.

am/pm

Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate.

A/P

Twelve-hour clock with the uppercase letter "A" or "P", as appropriate.

a/p

Twelve-hour clock with the lowercase letter "a" or "p", as appropriate.

AMPM

Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.

Blank space, + - $ ()

Use blank spaces, some math characters (+ -), and financial symbols ($ ¥ £) as needed anywhere in your format strings. If you want to use other common math symbols, such as slash (\ or /) and the asterisk (*), you must surround them in double quotation marks.

"Literal text"

Surround any text that you want users to see in double quotes.

\

Forces Access to display the character that immediately follows. This is the same as surrounding a character in double quotation marks.

*

When used, the character immediately after the asterisk becomes a fill character — a character used to fill blank spaces. Access normally displays text as left aligned and fills any area to the right of the value with blank spaces. You can add fill characters anywhere in a format string, and Access will fill any blank spaces with the specified character.

[color]

Applies a color to all of the values in a section of your format. You must enclose the name in brackets and use one of the following names: black, blue, cyan, green, magenta, red, 

Examples

The following are examples of custom date/time formats.

To format the date like…

Type this code into the Format property box:

2012-01-13

yyyy-mm-dd

2012

yyyy

13 Jan 2012

dd mmm yyyy

13.01.2012

dd\.mm\.yyyy (Access adds the backslashes automatically)

Fri 1/13

ddd m/d

2/6

ww/w

Jan 13, 2012

mmm d", "yyyy

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×