How to Change Date Format in MS Excel (2020)

October 13, 2020 5:12 pm

Edited by Piyush Kashyap, Reviewed by Gulshan

Microsoft Excel has established itself to be the standard software for most of the professionals. With all its versatile features, the software has accumulated more than one billion regular users.

The spreadsheet program has multiple applications that range from accounting, handling data, tracking inventory, and a lot more. As confusing the data might get sometimes, the display of date format might add up to the confusion.

There are multiple ways to display the same date, making it a bit of a task. Also, by default, the software always internally displays the date in the same format regardless of your customization.

If some of the most difficult Microsoft tasks are considered, changing numbers, dates, and time in MS Excel is on the top of the list.

Although they are common among data to be used in Excel, working with them is quite complicated. There are two basic reasons for this complexity in Excel.

One is, no matter how you want to save dates and time in Excel or how you have formatted the same in a given cell, it always stores in the same format internally as it does.

Another reason is that the display of time and date could be in a variety of ways. In furtherance of the same, let’s know-how dates and times are saved and stored in MS Excel.

So in Excel, the storage of data regarding time and dates is always in a sequential form, and this is due to a cell’s formatting only, that Excel shows the number as a ‘date and time’ or ‘time and date.’

The way Excel stores the date is the primary source of confusion. The dates stored in the software are in the form of integers. That is because, in Excel, by default, the date format is 1900.

Thus all the dates are represented in the form of integers displaying the number of days since 1st January 1900. The first day is stored as number 1, while the last day, 31st December 9999, is programmed to be displayed as 2958465.

Since the date format is 1900, the software does not recognize any date of the year before 1900. It would be accepted as ‘TEXT VALUE’ rather than a date.

For example, 42005 is January 1, 2015, since if you add up 42005 to January 1, 1990, it will show that date only. The similarity is the thing with the saving of time. Here time is saved as decimals that are between 0.0 to 0.99999.

The time in a day is shown as proportionate in decimals. For example, if it is showing 0.25 in Excel, it means the time is 6:00 A.M., whereas if it is showing 0.541655093, the time is 12:59:59. Now the combined way of time and dates that are shown in Excel is like a decimal number.

For example, for 1900 6:00 A.M., it will show 1.25. Similarly, for 2015 12:00 PM, it will show 24005.5.

How to Convert Date to Number in Excel?

There are two ways to understand the time and date shown in MS Excel as a serial number format:

Format Cells Dialog

The first step is to select the cell in which data is written in Excel and then press Ctrl+1. By doing this, the ‘format cells’ window will be opened. Now, move to the ‘General tab’ option that is given as the first option in the category list.

In case, you are curious to know the number of dates represented in the Excel as a serial number, and you don’t want to convert the date into a number then write the number shown in the Excel in the ‘Sample tab’ and then select ‘Cancel’ to close the window.

To change the date given in Excel with a serial number; Click ‘Okay.’

Excel Date value and Time Value functions

Another way of converting the decimal into time is the time value method. As per this method, you can use the TIME VALUE () function and convert the decimal represented in the Excel, into time. It is shown as = TIME VALUE (“6:60 A.M.”).

Similarly, for the date to be converted from the serial number represented in the MS Excel to get the real date, one can use DATEVALUE () function. In this function, you can write as =DATEVALUE (“1/1/2015”).

This method can also be used to get a combined result of both the time and the date represented as serial number and decimal in Excel, to time and date in an easy way. For that,write as =DATEVALUE ("1/1/2015") TIMEVALUE ("6:00 A.M.").

There is a point worth mentioning that the dates in the Excel starts from January 1, 1990, and there are no negative integers that could be recognized in the Excel, hence one cannot get the results, by using the above methods or any other method for that sake, of any date before January 1, 1990.

It begins from there only and cannot be recognized in the negative. For example, if you want to write a date like 12/31/1899, which is before January 1, 1990, in a sheet then it will only be saved as a text and not as a date.

In other words, in any case, one cannot achieve the usual date arithmetic, if the years fall before the prescribed date above. You will get the “#value! Error”, for every date that comes before January 1, 1990.

Default Date Format in Excel

There are some default window settings in our devices which are generally shown in the ‘format cell dialog’ window usually with an asterisk (*).

Whenever conversion or replacement of dates takes place in Excel or any work with the dates in done, the long date format is regained as the short date format from these ‘Window Regional Settings’.

While dealing with the date formats, the formats are restored from the Windows regional settings. The default date format in use is highlighted with an asterisk in the Format Cell dialog box. The default date settings change as soon as you change the settings.

How to Change the Default Date and Time Formats in Excel?

Different styles can be set as default time and date formats on a computer. For example, you can change the default date and timings style from the UK style of writing it, to the US style of writing the same and vice versa.

Now, in case you want to replace the set-up of time and date different from what is already set as default, go to ‘Control Panel’ and then select on the ‘Region and Language’ option provided there. From here, you will be redirected to the category page.

1
Choose the 'clock' option.
2
After this, choose ‘language and region’ and from there you will come to an option written as 'change the date, time or number format'.
3
Here in the format tab, move ahead by choosing the region provided under the format tab. Click on an arrow, which is provided in front of the format that you want to change. You are set to change the date and time.
4
A list of options will be provided to choose, select one of them, as your default setting. Now click on the option provided under the date and time format page through a link.

There may be a situation when you don't like any of the given options to set as the default date and time setup. In that case, there is an option to make it your way.

1
Find the option saying ‘Additional settings’ in your right side of the ‘region and language dialog window’ in the format tab.
2
Just click on that and you will be shown a different customized dialog window page.
3
Now, on this page, choose the ‘Date tab’ and then enter any or both custom format of the date, i.e., long date format or short date format in the box provided for the same.

How to Change Date Format in MS Excel

How to Quickly Apply Default Date and Time Formatting in Excel?

As already mentioned earlier, Microsoft Excel usually has two default formats for time and date. It works as a long date format and a short date format.

If you want to change the given default date format in Microsoft Excel steadily, then first go to select the dates you want to format. On the 'Home' tab, there are some number groups given where you are supposed to click on the arrow that is provided in front of the 'Number Format' box.

Now, select the desired format, as to which one is to be changed either short date format or long date format. Here if you are out of options and want more formatting options, you have two options.

You can either go through the list, scrolling it down to ‘More Number Formats’, otherwise go to the ‘dialog box launcher’ next to our Number tab. Just select any of those methods. Now you are on the ‘format cells dialog page,’ where you can easily change the date format as you want to.

There is one another way in case you want to immediately set the date format in Microsoft Excel to dd-mm-yy; press “Ctrl+Shift+#.” But for this, you need to be mindful of the fact that this method works only to set a date in the dd-mm-yy method.

For example, it will only set the date as 01-Jan-15 irrespective of where your window region settings are otherwise.

How to Change Date Format in MS Excel?

There are a variety of ways in which dates can be shown in Microsoft Excel. One of the easy ways is:

Format Cell Option:

One can use the Format cell option to change the format of the date being displayed.

1
Starting with selecting the date format which you want to change. You can either select it to convert into another format or just remove the cell’s written format to insert the dates in a new format.
2
Moving ahead, press Ctrl+1 so that the ‘format cells dialog’ could open. Otherwise, you can select the cells and right-click them to come to the ‘context menu.’ There also you can choose 'format cells' to open format cells dialog.
3
Now that you are on the Format cells window, you have to move ahead by selecting the ‘Number tab,’ and there will be a list of categories in front of you. In that given list, choose 'Date.’
4
On selecting the date, a panel will be opened on the desktop. Choose a setup format that you want to switch to under the ‘type category list.’
5
After doing this, a preview of the format that you chose to set in the sample box will be shown. It will show the sample of the first date in the selected format.
6
Now choose the preview by selecting the ‘OK’ option, if you found your desired date format. End the process by closing the window.

There is a possibility that you do not get the desired results in our Excel. That is, you have changed the default date format, but in the Excel sheet, it has not yet changed. The dates still formatted as text in Excel.

If such a case is there, before doing all of this, you need to change the dates in text format into date format.

Control Panel:

The date format displayed in the spreadsheet comes directly from the settings in the Control Panel.

1
Go to the Control Panel and click Region and Language.
2
Under the Format tab, choose Region.
3
Choose the date and time formatting by clicking on an arrow next to the format you want to change.
4
Select the desired one from the drop-down menu. The changes would be visible.

How to Convert Date Format to Another Locale?

At some point in time, you may want to change the date format as the one used in the locale. On a computer, you may have a foreign style of date format.

For example, you have a US pattern of date format written as month/day/year, and you want to convert it to the UK pattern of date formatting that is written as day/month/year, follow the below steps for doing so:

1
Select the cells or multiple cells you wish to change.
2
Directly press Ctrl+1 or right-click and manually open the Format Cell box.
3
Select the language you want from the available locations.
4
Click OK and save.

How to Create a Custom Date Format in Excel?

If the user is not satisfied with any of the predefined date formats available, there is an option to customize the date format manually. By applying the codes, the user can appropriately display the complete date.

1
In the spreadsheet, select the cell or multiple cells for which the date format is to be customized.
2
By pressing Ctrl+1 directly or by manual operation, open the Format Cell dialog box.
3
From the Number tab, select the Custom option from the Category list and enter the desired date format in the Type Box.
4
Click OK to save the changes.

There is an easy way to save a desired custom date format in Microsoft Excel. For this, go to the category list and click on the 'Date' option. From here, choose any one of the formats once you reach on the 'Type' option.

Now, type the desired pattern of custom date setting of the format in the ‘Type box’. Some of the codes used in setting custom date are; m, i.e., a month with no zero would be shown as 1, mm, i.e., a month with a zero before will be shown as 01, mmm, i.e., month in a short form to be shown as 'Jan,’ mmmm is the month in full form to be sown as 'January,’ etc.

Similarly, for the dates, d will show 1 that is a day without a prior zero, dd would be 01 whereas ddd will show as Mon (in case it is showing Monday), and the like. You can also use codes for writing hours.

Such as if you write h it displays as 0-23, that means hours without a leading zero, hh will show 00-23, s will show seconds such as 0-59, ss means 00-59, etc.

Below is the list of codes available for customization:

Codes for Day:

1
d- for the representation of day as 1-31
2
dd- for the representation of day as 01-31
3
ddd- Sun, Mon, Tue, Wed, Thu, Fri, Sat
4
dddd- Tuesday, Wednesday, etc.

Codes for Month:

1
m- for the representation of month as 1-12
2
mm- for the representation of month as 01-12
3
mmm- Jan, Feb, Mar, Apr, etc.
4
mmmm- April, May, June, July, etc.
5
mmmmm- Month as the first letter of the format.

Year Codes:

1
yy- to display as 00-99
2
yyyy- to display as 1998, 1999, etc.

Here, keep in mind that if you want to set up a custom format involving both date and time, you have to use ‘m’ just after ‘hh’ or ‘h’ as the case may be. Microsoft Excel will show minutes and not months. You can also use any of the characters like comma, colon, dash, and slash to create a custom date.

Shortcut to display the date in either short format or long format

1
Select the cell or column of cells to be displayed or customized.
2
In the Number group access Home option
3
Click the Format box option from the drop-down box.
4
Select either Short or Long date option.

Text Function

One other way to change the format of date is by using the TEXT function. The function uses two arguments to change the date as follows:

=TEXT(Value, “Code”)

The first argument is the entity meant to be converted, and the second one speaks about the format in which the conversion has to take place. This function is especially useful when the user intends to maintain the original column of date and display the other dates based on the original date.

For instance, if the first column in the spreadsheet displays dates in a specific format (U.S. Format), the TEXT Function could then be used to display the corresponding dates in a different format.

Excel 101: Text Function

How to use the Text Function

If the first column (column A) is assigned with dates in the format d/m/yyyy and the user needs to represent the date in the format of full-day, month and year, the formula will be 1= TEXT(A2, “mmmm dd, yyyy”)

The Text Function can be used in several ways to customize the date format. Apart from changing the date formats from short to long formats or customizing the same as per the wish, the function can also be used to change the Date format to a different locale.

Altering the delimiters:

Sometimes the user might need to use different delimiters to display the date. For instance, if the default delimiters in use are decimal points, but one prefers a slash, Excel allows them to do so.

1
Select the cell of multiple cells which you intend to change.
2
2.Press Ctrl+H to open the FIND & REPLACE Dialog box
3
Enter a decimal point in the ‘Find What field’ and a forward slash in ‘Replace With field.’
4
Click Replace all.

This would change the delimiters. A similar process can be opted for if the slash is to be replaced by a decimal point. Apart from the mentioned delimiters, the other delimiters which can be used are hyphen, space, etc.

How to Create a Custom Excel Date Format for Another Locale?

To show the custom date in a language different from default settings first, create a custom format and put the date as a prefix in their locale code correspondingly. Enclose the locale code in a square box and then put a dollar sign with a dash after it.

For example, [$-1009]-English, Canada, [$-804]- Bengali, India, [$-409]- English, United States, etc.

How to Fix if Excel Date Format is not working?

Although it is rare to face any difficulty while working with Excel, there are some easy ways to solve and fix the issues in case it happens. If Excel shows (######) signs in place of dates, there might be some problems with the size.

Go to the right border of the column and double click to change the size. It will automatically change to size fit to include date format in it. Otherwise, also you have the option of dragging the border to set the width you want.

Another issue with these hash marks may be that the date or time format you are putting contains a negative value. To resolve this issue, you have two options:

Firstly, you can switch to the 1904 date system by choosing the following steps.

1
Open the file, go to ‘Advanced’ after selecting ‘Options.’
2
Go down the list and choose ‘when calculating this workbook section’ and opt for ‘Use 1904 date system’.

You may switch to the 1904 date system.Go to Files>Options>Advanced, scroll down, and choose the 1904 Date system. In this system, 0 is 1-Jan-1904, and so on. Likewise, -1 is represented as -2-Jan-1904. While this representation takes time to get used to, it is the most appropriate way if the calculations involve early dates.
Another way to insert negative values in dates is by using the TEXT function.For instance, if the column C1 is greater than column B1, then to enter the negative values, the user may use the formula =TEXT(ABS(B1-C1),”-d-mmm-yyyy”)

3
Click ‘OK.’ In such a system, 0 is shown for 1-January-1904, 1 is for 2-Jan-1904, and likewise.

Sometimes the dates imported to Excel are not in the Date format but are imported as Text format. Another method to resolve this issue is to use the “EXCEL TEXT function.” This method allows you to show negative numbers in your dates and time.

Keep in mind that this method cannot be used in other calculations since the Text function in Excel returns a text value of the date or time provided. Whereas the previous method i.e., ‘Use 1904 date system’ can be used for other calculations as well.

If there is an issue of dates being imported as text values in Excel, then, in that case, you can change the text dates into date format by using the given Excel ‘DATEVALUE FUNCTION’ or you can directly use the Excel ‘text to column’ feature.

Such a situation may occur if you have imported some data in Excel from a source like .csv file or some of the other like external databases because dates are usually imported as text values rather than in a date format. Numerous articles on the internet could help one to understand the application of the Devalue function.

Piyush

Follow me here

Share0

Tweet0

Share0

About the Author

Piyush Kashyap is a Ph.D student at Sant Longowal Institute of Engineering and Technology, Sangrur. He is a budding editor/ writer and has been working as a part-time reviewer for online content. He loves to read tech-based articles and has a knack for reviewing such articles He likes to stay updated about the latest trends in technology. He has also been working as a reviewer for many scientific journals. He also writes articles based on science. Know More About Piyush

Learn more