- Category: Microsoft Excel
- Created: Monday, 27 June 2016 16:24
If you wish to change the way the date looks in an Excel spreadsheet, you can change the format with a few simple steps.
In Excel by default you can choose to show a short date (6/01/2016) or a long date (06 January 2016). You can customise how the date is displayed by using different formats, this could include showing the day of the week in your date, i.e. Wednesday 6 January 2016.
Here's how to do it...
- Highlight the cells that contain the dates you wish to format.
- Open the Format Cells dialog box, to do this you can do one of the following:
- Right mouse click in the highlighted cells and select Format Cells from the presented menu,
- or, Press Ctrl+1 (the number one),
- or, Click the Format drop-down arrow (button circled in blue below) and select More number formats,
- or, Click the dialog box expander button (button circled in black below)
- From the Format Cells dialog box, click the Number tab at the top of the dialog box and select the Custom category.
- In the Type box, type the combination of date codes to create a date format, see the Date Format Example table below to change the format of the date.
- Click OK.
Date Format Examples
The date is represented in Format cells as combinations of d=day or day number, m=month or month number, y=year digits.
To show examples we will use: Wednesday 6 January 2016.
|d||6||Days as 0 to 31|
|dd||06||Days as 01 to 31|
|ddd||Wed||Days as Mon to Sun|
|dddd||Wednesday||Days as Monday to Sunday|
|m||1||Month as 1 to 12|
|mm||01||Month as 01 to 12|
|mmm||Jan||Month as Jan to Dec|
|mmmm||January||Month as January to December|
|mmmmm||J||Month as first letter of month|
|yy||16||Years as 00 to 99|
|yyyy||2016||Years as 1900 to 9999|
|COMBINED DATE CODES||DISPLAYS|
|dd mmm yy||06 Jan 16|
|ddd, dd-mmm/yy||Wed, 06-Jan/16|
|dddd dd mmmm yyyy||Wednesday 06 January 2016|
To display the current date in a cell Press Ctrl+;
To use the current date format in a date cell Press Ctrl+Shift+#
If a cell displays ###### in a cell after formatting the date, widen the column to show the date.