Excel Tip - Save the Date - Formatting Dates

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


Date Examples

dd mmm yy 06 Jan 16
d-mm-yyyy 6-01-2016
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.

Pin it