How to Format Number, Currency, and Date/Time Fields in Access
A field’s Format property changes how information appears in the field, not how the data is actually stored in the field. For example, a date field could be formatted to display the same value as 6/10/2020; Saturday, June 10, 2020; or 10-Jun-20. Each field type has its own set of formats.
There are two ways to format a number, currency, and date/time field:
Selecting a ready-made format from the Format list (the easy way). For most people, the ready-made formats listed in the Number and Date/Time Formats table below will be all you will ever need to format your fields.
Typing a series of formatting characters by hand in the Format box (the more difficult way).
If none of the ready-made number, currency, or date/time formats meet your needs, you can format fields the old-fashioned way—by hand.
In Design View, click the field you want to format.
Click the Format box in the Field Properties section.
You can use the table below to help you know what characters to enter. You can mix and match any of the characters—for example, you could add “mmmm” (full name of month) to “yy” (last two digits of the year) to get “January 00.”
Enter the formatting characters for how you want the number to be formatted.
Number, Currency, and Date/Time Operators Formatting Characters
Character
Description
Format
Display
:
Time separator
h:nn
8:45
/
Date separator
m/d/yy
10/8/17
-
Date separator
m-d-yy
10-8-17
d
Day in one or two numeric digits
m/d/yy
10/8/00
dd
Day in two numeric digits
m/dd/yy
10/08/00
ddd
First three letters of the weekday
ddd, m/d/yy
Sun, 3/8/00
dddd
Full name of the weekday
dddd, m/d/yy
Sunday, 3/8/00
m
Month in one or two digits
m/d/yy
3/15/00
mm
Month in two digits
mm/dd/yy
03/15/00
mmm
First three letters of the month
mmm-d-yy
Mar-15-00
mmmm
Full name of the month
mmmm d, yyyy
March 15, 2000
yy
Last two digits of the year
m/d/yy
3/15/00
yyy
Full year
mmmm d, yyyy
March 15, 2000
h
Hour in one or two digits
h:nn
8:45
hh
Hour in two digits
hh:nn
08:45
nn
Minute in two digits
hh:nn
13:09
ss
Second in two digits
hh:nn:ss
10:45:07
AM/PM
Twelve-hour clock (uppercase)
hh:nn AM/PM
08:45 AM
am/pm
Twelve-hour clock (lowercase)
hh:nn am/pm
08:45 am
Number, Currency, and Date/Time Operators Formatting Characters