How to Change the Date Format in Excel?

Excel provides various formats to display a date. It even allows you to change date format in a way you are comfortable with. Excel has defined the code for displaying the days, months and years using a combination of which you can format or customize your dates as you wish.

In this section we will see how the dates are stored in excel, how can you change its format? How you can customize the date format if do not want to go with the available date formats? What is default date format and how you can change it?

The topics we will discuss are as follow:

1. Dates in Excel
2. Convert Date to Number
3. Change the Date Format in Excel
4. Customize the Date Format in Excel
5. Change Default Date Format

Dates in Excel

Whenever we write a date in an Excel sheet, it stores it in the form of a number. Let us take an example of a date 1/01/2019. To check how it is stored in the number form in Excel. Type the example date in a cell in an excel sheet and press Control+1 or you can right-click on the cell and select Format Cells…. Both will lead you to a Format Cells dialog box.

Now here in the Number tab, select General under the Category at the right side in Sample box you can see the 43466.

This date is numbered 43466 because the Excel has numbered the date 01-01-1900 as 1. So, from 01-01-1900 1/01/2019 is the 43466^th day.

Formula to obtain today’s date/current date from the system:

=TODAY()            (press Enter)

Code to display a date in various format:

Steps to Change Date Format in Excel 2016:

Convert Date to Number

Well we can convert the date into a number using the Format Cells dialog box or we can normally go to the Numbers group of Home tab and in the Format dropdown box select General for the selected cell where we have written the date.

Or

We can use the formula to convert the date into the number. For that select a cell and type the formula:

=DATEVALUE(“01-01-2019”)

Change the Date Format in Excel

To change the date format in Excel, select the cell which has the date, & of which you want to change the format. Now, pull up the Format Cells dialog box either by pressing the Ctrl+1 or by right click on the cell and select Format Cells.

Under Number tab in Category select Date, at the right side, you have the Type box under which you can select the date format among the various options.

You can also set the language in which you want to display the dates in Locale.

Note: The date formats that start with an asterisk sign (*) are the default date format of the Excel. Whenever you write a date in a cell, it gets formatted in the default format of the excel.

Customize the Date Format in Excel

Now, here you can construct your own date format if you are not comfortable with the date formats available. For this pull out your Format Cells dialog box. Under Number tab in Category, select Custom.

At the right side in the Type box type your customized date format. For example, ddd, mmm/dd/yy this will display the date in the format, Tue, Jan/01/19.

Change Default Date Format

To change the default date format, we have to make changes to the operating system date format which we can do via Control Panel.

Open the Control Panel and click on Clock, Language and Region>Region.

A Region dialog box will open and in the Formats tab, you can change the language in Format drop-down box. In the Date and time formats, you can modify the current Date and time formats. If required you can also go to Additional settings.

The changes you implement here will be visible in the Excel default date format. So this is all about how you can change the format of the dates in Excel.