Create a field to store dates and times

To add a date/time field to a table in a desktop database, open the table in Design view, click the first empty box under Field Name, then enter a name for the new field, and then click the adjacent Data Type box and choose Date/Time from the list.

Note:  In an Access web app, you use the Tile Pane to create tables, but after you create one you can open it in Design view – just right-click it and then click Edit Table.

In a web database, open the table in Datasheet view, click Fields, and then in the Add & Delete group, click Date & Time.

After you create a date/time field, you can add it to a form using the Field List. Open the form in Design view or Layout view, and press Alt + F8 to display the Field List. At the top of the Field List, click Show all tables, and then double-click the Date/Time field to add it to the form.

When you add a Date/Time field, you can specify what date format you want for the field. You can apply a predefined format or create a custom format. Read on to learn more.

In this article

Overview

Add a Date/Time field to a table

Apply a format to a Date/Time field

Guide date/time data entry using an input mask

Predefined and custom formats you can use

Overview

Access stores date/time data as decimal numbers: the integer part of the value represents the date, and the rest of the number indicates the time. Because date/time data is numeric it’s easy to perform calculations, such as determining age.

Display formats

Most of the time, people don’t want to see the actual value of a date/time field – it doesn’t look like a date or a time to a person. When you use a date/time value, Access displays it in an easy-to-read format, such as 11/22/2011. If you don’t give Access a format to use, it automatically displays the date and time in the General Date and Long Time formats.

Valid range of values

Access doesn’t accept dates that fall outside the supported range of values – but unless you’re an archaeologist, historian or the like, you’re unlikely to need to use such dates. A date/time value can range from -657,434.0 (January 1, 100 A.D. 00:00:00) to 2,958,465.9999 (December 31, 9999 A.D. 23:59:59).

If you enter a date outside of that range or if Access doesn’t recognize your input as a date, an error message gives you the choice of entering a new value or converting the field to text.

For more information on the date/time data type, see the article Introduction to data types and field properties.

Differences in an Access web app or a web database

There are a few things about Date/Time fields that don’t work the same in an Access app or a web database as they work in a desktop database. When working in an Access app or web database, bear in mind these limitations:

  • You don’t have as many pre-defined date format to choose from as you do in a desktop database – but you can enter a custom format that mimics a pre-defined format.

  • Table Design view is not available in a web database – but you can make table design changes, including adding a Date/Time field, in Datasheet view.

  • You can’t use input masks. In an Access app you can use input hints, but they don’t guide input – they fade when you start to enter data.

Top of Page

Add a Date/Time field to a table

In an Access app or a desktop database, you use Design view to add a field to a table. In a web database, you use Datasheet view.

Add the field to an app or a desktop database

  1. Open the table in Design view.

  2. In the next available row, type a name for your new field in the Field Name box.

  3. In the Data Type box, choose Date/Time from the list.

Adding a Date/Time field in Design view

Add the field to a web database

  1. Open the table.

  2. Click Fields, and then in the Add & Delete group, click Date & Time.

  3. Type a name for the new field.

Adding a Date/Time field in Datasheet view

Top of Page

Apply a format to a Date/Time field

Access provides several predefined formats for the date and time data, but if these formats do not meet your needs you can specify a custom format. The default format is the General Date format — m/dd/yyyy h:mm:ss AM/PM.

Note:  This section doesn’t apply to web databases. In a web database, you only have two choices of date format: Short Date and Long Date. You set the format in Datasheet view – with the Date/Time field selected, click Fields, and in the Properties group, click Format and choose the one you want.

Apply a predefined format

  1. Open the table in Design view. If you don’t already have a Date/Time field, add one (see the previous section for steps).

  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.

    The Format property of a Date/Time field

  4. After you select a format, the Property Update Options smart tag 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.

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

Apply a custom format

To create a custom format, you enter a pattern of characters in the Format property of the field. Custom formats for the Date/Time fields can contain two sections — one for the date and another for time — and you separate the sections with a semicolon.

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 semicolon. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy;h:mm:ss. To apply a custom date or time format, complete the following steps:

  1. In the Navigation Pane, right-click the table that you want to work with, and then click Design View.

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

  3. In the lower section, click the Format property box, and then enter your custom format using characters from the Custom format placeholders and separatorstable.

  4. After you enter a format, the Property Update Options smart tag 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.

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

Top of Page

Guide date/time data entry using an input mask

When you want users to enter date/time data in a specific format, use an input mask. Input masks can be applied to fields in tables, query result sets, and to controls on forms and reports. An input mask doesn’t directly affect the way the data is displayed – that’s determined by the format, as discussed in preceding sections.

Note:  You can’t create an input mask in an Access app or in a web database.

Add an input mask

  1. Select the Date/Time field, and then in the lower section of the design grid, on the General tab, click the Input Mask property.

  2. The Input Mask Wizard opens and guides you through selecting an Input Mask format.

  3. To apply the format, click Yes, and then click Save.

Top of Page

Predefined and custom formats you can use

Predefined formats, with examples

Format

Description

Examples

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/2015 10:10:42 AM

Long Date

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

Monday, August 29, 2012

Medium Date

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

29/Aug/12
29-Aug-12

Short Date

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

8/29/2012
8-29-2012

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 format placeholders and separators

Use any combination of the following components to build a custom format. For example, to display the week of the year and day of the week, type ww/w.

Important:  Custom formats that are inconsistent with the date/time settings specified in Windows regional settings are ignored. For more information on Windows regional settings, see Windows Help.

Date format components

Format

Description

d

Day of the month in one or two numeric digits, as needed (1 to 31).

dd

Day of the month in two numeric digits (01 to 31).

ddd

First three letters of the weekday (Sun to Sat).

dddd

Full name of the weekday (Sunday to Saturday).

w

Day of the week (1 to 7).

ww

Week of the year (1 to 53).

m

Month of the year in one or two numeric digits, as needed (1 to 12).

mm

Month of the year in two numeric digits (01 to 12).

mmm

First three letters of the month (Jan to Dec).

mmmm

Full name of the month (January to December).

q

The quarter of the year (1 to 4).

y

Number of the day of the year (1 to 366).

yy

Last two digits of the year (01 to 99).

yyyy

Full year (0100 to 9999).

Time format components

Format

Description

h

Hour in one or two digits, as needed (0 to 23).

hh

Hour in two digits (00 to 23).

n

Minute in one or two digits, as needed (0 to 59).

nn

Minute in two digits (00 to 59).

s

Second in one or two digits, as needed (0 to 59).

ss

Second in two digits (00 to 59).

Clock format components

Format

Description

AM/PM

Twelve-hour clock with the uppercase letters "AM" or "PM," as appropriate. For example, 9:34PM.

am/pm

Twelve-hour clock with the lowercase letters "am" or "pm," as appropriate. For example, 9:34pm.

A/P

Twelve-hour clock with the uppercase letter "A" or "P," as appropriate. For example, 9:34P.

a/p

Twelve-hour clock with the lowercase letter "a" or "p," as appropriate. For example, 9:34p.

AMPM

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

Separator components

Note:  Separators are set in the regional settings of Windows.

Separators are displayed as part of a date/time value. In addition to the standard date (/) and time (:) separators, you can use any short string of characters enclosed in quotation marks as a custom separator. The quotation marks are not displayed. For example, "," displays a comma.

Separator

Description

:

Separator for time formats; for example hh:mm

/

Separator for date formats; for example mmm/yyyy

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×