Concept: Dates and Times
When possible, Worksheets automatically displays
dates using standard date formatting. In some cases, such as formula results,
Worksheets shows the date as a serial number; you can use to display the result using date formatting.
In the US English locale, you can specify dates as described in this section. Other locales
might have different entry formats.
You can specify dates as shown in the table below, where:
- MM represents a month number from 1-12.
- DD represents a day number in a month from 1-31 depending on the month.
- YY represents a 2 digit year where 30 = 1930, 29 = 2029, and 00 = 2000.
- YYYY represents a 4 digit year (from 1899 to 9999).
- MMM represents the abbreviated month name (such as Jan, Feb, and Mar).
- MMMM represents the full month name (such as January, February, and March).
Format | Date |
|---|---|
MM/DD/YY | 7/16/18 |
MM/DD/YYYY | 7/6/2018 |
MM-DD-YY | 7-6-18 |
MM-DD-YYYY | 7-5-2018 |
YYYY-MM-DD | 2014-07-08 |
DD-MON-YY | 12-JAN-12, 1-DECEM-99 |
DD-MON-YYYY | 12-MAY-2012, 14-MARCH-1998 |
MON-DD | Jul-4, Feb-28 |
You can specify times as shown in the table below, where:
- hh represents an hour of the day from 0-23
- mm represents a minute from 0-59
- ss represents a seconds value from 0-59
- ms represents milliseconds from 0-999
- am/pm is AM or PM (case insensitive)
You can append am or pm to the time. Example: 11:45am, 4:55pm.
Format | Example |
|---|---|
hh:mm | 22:30, 11:45 |
hh:mm:ss | 13:14:15, 9:00:0 |
If the AM/PM designator is missing, Worksheets defines times before 12:00 as AM and times
after 12:00 as PM. If the hour value is 13 or higher, Worksheets defines the time as
24-hour (military) time and does not allow the AM/PM designator.
You can combine any of the date formats with any of the time formats to get a specific date
and time combination such as 7/4/2014 13:15 or 09-Jul-2000 10:26:46 am.
Normally if you type a date such as
Dec 1, 2018 into a cell, Worksheets converts it into the datetime value
12/01/2018. To display the value as text in the cell, type a single quote (')
before the value:
'Dec 1, 2018
.