Format a date and time field

https://support.office.com/en-us/article/format-a-date-and-time-field-47fbbdc1-52fa-416a-b8d5-ba24d881b698

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

Question: In Microsoft Access 2003/XP/2000/97, how can I define a custom format for a Date/Time field on a form? I want to display the date as "01-JAN-2005 21:00:00", even when I move the cursor to another field.

Answer: In Access, you can use the Format property to customize how a date will appear in a form.

For example, if you wanted to view the date as "01-JAN-2005 21:00:00", you could enter the following into the Format property on your text box:

dd-mmm-yyyy h:nn:ss

Then when you viewed the form, the text box would display the date as follows:

If you wanted to view the date as "01-JAN-2005 9:00:00 PM", you could enter the following into the Format property on your text box:

dd-mmm-yyyy h:nn:ss AM/PM

Then when you viewed the form, the text box would display the date as follows: