fbpx

Work like a Pro Series. Use Date Formatting in Excel

In this Work like a Pro Series we demonstrate how MS Excel manipulate dates in Excel in many various ways and show you how to best utilise the functions available to make your lists a lot clearer.

Here goes, a bit of ‘not so common’ knowledge:

When you enter a date (e.g. 1/12) in Excel, Excel will treat it as a date field and display it in the default date format (i.e. 1-Dec). And if you don’t enter the ‘year’, Excel will assume you are referring to the current year (e.g. 1/12 is treated as 1/12/2015).

There are many other ways to display the same date; the following diagram shows the various formats of the same input value (i.e. 1/12):

Date formatting in Excel A10

Pre-defined Date Formats

Excel has a number of pre-defined date formats that you can choose from. To choose from the pre-defined list:

  1. Select the cell (or cells) to format.
  2. Press CTRL-1, a Format Cells windows will come up.
  3. Click on the Number tab and then click Date under Category.
  4. You can now pick a date format under Type. You can preview the output in the Sample box.

Date Formatting in Excel Format Cells

Custom Date Formats

You can also customise your date format such as displaying weekday only. To customise a date field, you need to know these basic formatting codes:

To displayFormatting Code
Months as 1-12m
Months as 01-12mm
Months as Jan-Decmmm
Months as January- Decembermmmm
Days as 1-31d
Days as 01-31dd
Days as Sun-Satddd
Days as Sunday-Saturdaydddd
Years as 00-99yy
Years as 1900-9999yyyy

To format:

  1. Select the cell (or cells) to format.
  2. Press CTRL-1, a Format Cells windows will come up.
  3. Click on the Number tab and then click Custom under Category.
  4. Enter the formatting code under Type.  You can preview the output in the Sample box.  

The following diagram shows the formatting code to display the weekday (Tuesday) of the date 1/12/2015.

Date formatting in Excel Format Cells Number 2

The formatting codes that we use in the first diagram are listed below:

Date formatting in Excel A and B columns

We hope you have found this feature helpful. If you have any questions, contact our friendly team of experts or send us an email at [email protected]

Stay tuned for regular updates.

Warm regards,

The itconnexion Team.

We can help you!

In case you’re still unsure about the process or if you need further assistance, feel free to give us a call or drop us an email. Our team of experts will be sure to offer a helping hand.