CONTACT US

1300 89 22 00

white x 

Monday, 03 August 2015 11:14

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 display Formatting Code
Months as 1-12 m
Months as 01-12 mm
Months as Jan-Dec mmm
Months as January- December mmmm
Days as 1-31 d
Days as 01-31 dd
Days as Sun-Sat ddd
Days as Sunday-Saturday dddd
Years as 00-99 yy
Years as 1900-9999 yyyy

 

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, as always please don't hesitate to ask via our comments section on this blog post.

Stay tuned for regular updates.

 

Warm regards,

The itconnexion Team.

 

Add comment


Security code
Refresh

Socialise with us

Back to top