Formatting Date and Time

You can format date and time in many different ways. Look at the following example to learn how to do it.

Example 1:

Put the following date inside a cell: February 14, 2014 18:15:00. Right-click the cell, and then select Format Cells….

In the Format Cells window, the Number tab consists of four parts: Category, Sample, Type and Locale (location).

Click the Date position inside the Category box. On the right side, inside the Type box, different types of date and time formatting options will be displayed. Click one of them and then in the Sample box you will see the preview of the currently selected date, with the new formatting applied.

Create your own date and time formats

There is a large number of combinations when it comes to date and time formatting, but sometimes you won’t find any that meets your expectations. In such situations, Excel will let you create your own format that you can use later when working in Excel.

Example 2:

Click the cell again and then open the Format Cells window. Choose the Custom item, which is located at the bottom, in the Category field.

You can format date and time by using many different combinations. In the table below, you can see which letters correspond to which type of formatting.

For Date:

Years: 00-99y
Years: 1900-9999yyy
Months: 1-12m
Months: 01-12mm
Months: Jan-Decmmm
Months: January-Decembermmmm
Months: F-Dmmmmm
Days: 1-31d
Days: 01-31dd
Days: Mon-Sunddd
Days: Monday-Sundaydddd

For Time:

Hours: 0-23h
Hours: 00-23hh
Minutes: 0-59m
Minutes: 00-59mm
Seconds: 0-59s
Seconds: 00-59ss
Time: 10 AMh AM/PM
Time: 10:36 AMh:mm AM/PM
Time: 10:36:24 Ah:mm:ss A/P
Time: 10:36:24.75 AMh:mm:ss.00

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.