Format the date and time field in Access

When you use the Date/Time data type in Microsoft Access, you have the option to keep the default formats, apply a predefined format, or create a custom format. This article briefly explains the Date/Time data type, how to add a date and time field to a table and how you can apply a predefined or custom format.

What do you want to do?

Overview of date and time formats

Add a Date/Time field to a table

Format the Date/Time field

Restrict date and time entry format by using an input mask

Additional information

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. were 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 but you can display the date and time in several formats.

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.2006 12:07:12 PM or 28/11/2006 12:07:12 PM. In the United States, you would see 11/28/2006 12:07:12 PM. It is possible to change these automatic formats by using custom display formats. The display format that you select will not affect how the data is entered or how Access stores that data. For example, you can enter a date in a European format such as 28.11.2006, and have the table, form, or report display the value as 11/28/2006.

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.2006, 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.

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

Top of Page

Add a Date/Time field to a table

1. Open the table where you want to insert the Date/Time field in Datasheet view.

2. Click the down arrow next to Click to Add column and select Date & Time from the drop down field list.

3. Rename your field and save the table.

To add dates to the field, just click the Date Picker or calendar icon and do one of the following:

• To enter the current date, click Today.

• To select a day in the current month, click the date.

• To select a different month and day, use the forward or back buttons.

Top of Page

Format the 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.

Apply a predefined format

1. If you don't already have a date and time field, add the field. Open the table in Datasheet view, click the down arrow next to Click to Add column, and select Date & Time from the drop down field list.

2. Right click the table header and switch to the Design view.

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

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

   

5. 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.

6. 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.

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

Apply a custom format

When you create a custom format, you enter various characters in the Format property of a table field. The characters consist of placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors. 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 by using characters from the Placeholders and separators for a custom format table.

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

Restrict date and time entry format by using an input mask

When you want to force users to enter 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.

You can define an input mask in one format but set a different display format. For example, you can have an input mask require the date to be entered as 2010.04.15, but have the date display as, 15-April-2010.

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

Additional information

Predefined formats and examples

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/2008 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, 2006
Medium Date	Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings.	29/Aug/06 29-Aug-06
Short Date	Displays date values, as specified by the Short Date format in your Windows regional settings.	8/29/2006 8-29-2006
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

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	De s cription
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.

Any short string of characters, enclosed in quotation marks ("") is a custom separator. The quotation marks are not displayed. For example, "," displays a comma.

:   Time separator. For example, hh:mm

/   Date separator. For example, mmm/yyyy

Top of Page