Format dates

Access provides several different methods for formatting dates, depending on which part of Access you're working in. On forms and reports in desktop databases, dates are usually shown in text boxes. Just set the Format property for the text box to the date format you want. In Layout view or Design view, press F4 to display the Property Sheet, and then set the Format property to one of the predefined date formats:

Or, enter a custom date format.

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

Note:  Access apps are more restrictive about what formats you use, so unfortunately you can't use custom date formats like those described below.

Top of Page

In tables

If you're viewing dates in a table datasheet (either in a desktop database or Access app), here's how to change the date format:

1. Right-click the table in the Navigation Pane and then click Design View. If the Navigation Pane isn't already displayed, press F11 to display it.

2. Select the date field, and then in the Field Properties list at the bottom of the screen, select the format you want from the Format property drop-down list. If you are using a desktop database, you can also enter a custom date format.

Note:  Any format settings you make here (at the table level) won't change the formatting on other existing objects based on that table. 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.

Top of Page

In queries

If a date field in a query isn't formatted the way you want, switch to Design view, right-click the date field, and then click Properties. In the Property Sheet, select the format you want from the Format property list.

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

Top of Page

In expressions

In desktop database expressions, 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.

Note:  The FormatDateTime function isn't available in Access apps.

Top of Page

Custom date formats

Sometimes, the Format property drop-down list in the Property Sheet doesn't contain the exact date format you want. In a desktop database, you can create your own custom format by using a type of code that Access recognizes for date formatting. You just type the code right into the Format property box. 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

See this Format property article for more examples of custom and built-in date formats.

Top of Page