Format a date and time field

The Date/Time Data Type has a wide variety of formats to help meet your unique circumstances. With Date/Time formatting you have three choices: keep the default formats, apply a predefined format, or create a custom format.  When you apply a format to a table field, that same format is automatically applied to any form or report control that you subsequently bind to that table field.  Formatting only changes how the data is displayed and does not affect how the data is stored or how users can enter data.

In this article

Overview of date and time formats

Apply a predefined format

Examples of predefined formats

Apply a custom format

Examples of custom formats

Custom format characters

Set a Date/Time format in an Access Web App

Overview of date and time formats

Access automatically displays the date and time in the General Date and Long Time formats. The dates appear as, mm/dd/yyyy in the U.S. and as, dd/mm/yyyy outside the U.S. where mm is the month, dd is the day, and yyyy is the year. The time is displayed as, hh:mm:ss AM/PM, where hh is the hour, mm is minutes, and ss is seconds.

These automatic formats for dates and times vary depending on the geographic location specified in the Microsoft Windows Regional and Language Options setting on your computer. For example, in Europe and many parts of Asia, depending on your location, you might see the date and time as 28.11.2018 12:07:12 PM or 28/11/2018 12:07:12 PM. In the United States, you would see 11/28/2018 12:07:12 PM.

It is possible to change these predefined formats by using custom display formats. For example, you can enter a date in a European format such as 28.11.2018, and have the table, form, or report display the value as 11/28/2018. The custom format that you select will not affect how the data is entered or how Access stores that data.

Access automatically provides a certain level of data validation related to date and time formatting. For example, if you enter an invalid date, such as 32.11.2018, a message appears and you would have the option of entering a new value or converting the field from the Date/Time data type to the Text data type. Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). Valid time values range from .0 to .9999, or 23:59:59.

When you want to constrain entry of date and time in a specific format, use an input mask. For example, if you applied an input mask to enter dates in the European format, someone entering data in your database will not be able to enter dates in other formats. Input masks can be applied to fields in tables, query result sets, and to controls on forms and reports. For more information, see Control data entry formats with input masks.

Top of Page

Apply a predefined format

Access provides several predefined formats for date and time data.

In a table

  1. Open the table in Design View.

  2. In the upper section of the design grid, select the Date/Time field that you want to format.

  3. In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list.

    An image of the Property Sheet with the Format drop-down list displayed.

  4. After you select a format, the Property Update Options button appears, and lets you to apply your new format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere <Field Name> is used. In this case, Field Name is the name of your Date/Time field.

  5. To apply your changes to the entire database, when the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes.

    For more information, see Propagate a field property.

  6. Save your changes and switch to Datasheet view to see whether the format meets your needs.

Note    New forms, reports, or views that you create based on this table get the table’s formatting, but you can override this on the form, report, or view without changing the table’s formatting.

In a form or report

  1. Open the form or report Layout View or Design View.

  2. Position the pointer in the text box with the date and time.

  3. Press F4 to display the Property Sheet.

  4. Set the Format property to one of the predefined date formats.

In a query

  1. Open the query in Design View.

  2. Right-click the date field, and then click Properties.

  3. In the Property Sheet, select the format you want from the Format property list.

In an expression

  • Use the FormatDateTime function to format a date value into one of several predefined formats.

    You might find this helpful if you are working in an area that requires an expression, such as a macro or a query.

Top of Page

Examples of predefined formats

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

Top of Page

Apply a custom format

Sometimes, the Format property drop-down list in the Property Sheet doesn’t contain the exact date format you want. You can create your own custom format by using a type of code that Access recognizes for date formatting.

When you apply a custom format to the Date/Time field, you can combine different formats by having two sections, one for the date and another for the time. In such an instance, you would separate the sections with a space. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy h:mm:ss.

  1. Open the table in Design View.

  2. In the upper section of the design grid, select the Date/Time field you want to format.

  3. In the Field Properties section, select the General tab, click the cell next to the Format box and enter the specific characters based on your formatting needs.

  4. After you enter a format, the Property Update Options button appears and lets you apply the format to any other table fields and form controls that would logically inherit it. To apply your changes throughout the database, click the smart tag, and then click Update Format everywhere Field Name is used. In this case, Field Name is the name of your Date/Time field.

  5. If you choose to apply your changes to the entire database, the Update Properties dialog box appears and displays the forms and other objects that will inherit the new format. Click Yes to apply the format.

    For more information, see Propagate a field property.

  6. Save your changes, and then switch to Datasheet view to see whether the format meets your needs.

Top of Page

Examples of custom formats

Here are some examples of different ways to format January 13, 2012:

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

Top of Page

Custom format characters

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.

Top of Page

Set a Date/Time format in an Access Web App

Access Web Apps display data in “views”, which have text boxes much like forms in desktop databases. However, instead of a property sheet, you set formatting options by selecting the text box and then clicking the Format button that appears. Then, select the format you want from the Format list.

Access apps do not have custom date formats. You can create queries in Access apps, but there aren’t options for formatting date columns within the query. All date formatting is controlled at the view level. Lastly, the FormatDateTime function isn’t available in Access apps.

Image of the Formatting menu in an Access app.

Top of Page

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.

×