How to Change Date Format in Excel?

I want to tell you that Excel is a strong assistant, but date management usually has some issues. Have you ever typed a date and then it changes, and you wonder how the heck it did that? Frustrating, right? Knowing how to make changes on your Excel spreadsheet, such as how to change the date format, helps you consolidate your work and get good-looking documents.  

From changing the display format from being “dd/mm/yyyy” to “mm/dd/yyyy” or even dates being displayed in some other fancy format, Excel has innumerable options if one knows where to search for them. 

It’s not just about the form – and, indeed, learning How to Change Date Format in Excel is not just about making the data prettier; it’s about making the data clearer.  Whether the data are financial reports or project plans, formatted dates facilitate, if not enhance, comprehension of the details; now let’s look at how easy it is to conquer date formatting!

Table of Contents

1) What is Excel Date Format?

2) How to Convert Date to Number in Excel?  

3) How to Change Date Format in Excel? 

4) How to Create a Custom Excel Format?

5) How to Change a Data Format to any other Locale?

6) Reasons you’re unable to change your Data Format in Excel  

7) Conclusion

What is Excel Date Format?

People who use Excel regularly should know how to change date format in Excel. It helps them to save time at work. However, it's important to note that Excel stores default time and date, which leads to confusion.   

Although Excel users assume Excel remembers the month, year, and day of a particular date, it’s not true. The dates in Excel are stored as a sequence of numbers. Next, the display of a number as a date and time or a date, time occurs due to a cell’s formatting.
 

Dates in Excel 

Dates in Excel are stored in the form of integers. These integers represent the total number of days. The days are counted from January 1, 1900, to December 31, 9999. So, the days are stored in Excel as 2958465. Under the system, the days are mentioned as follows: 

  a) 2-Jan-1900 is 2 

  b) 3-Jan-1900 is 3 

  c) 1-Jan-2015 is 42005 (it is counted as 42005 because it counted after 1 January 1900)  

Sign up and learn the top 10 Excel shortcuts with our Microsoft Excel Masterclass!

Time in Excel

It is important to understand the way time is represented in MS Excel, as it can get a bit confusing. In Excel, it is presented as decimals between .0 and .99999. This represents a portion of a day in which .0 means 00:00:00 and .99999 means it is 23:59:59. For instance,

  a) 06:00 AM is 0.25

  b) 12:00 PM is 0.5

  c) 12:59:59 PM is 0.541655093

Dates & Times in Excel

Excel represents dates and time in different formats. Both are stored as decimal numbers, which consists of the date in the form of an integer and the time via decimal portion.For instance:

  a) 1.25 is January 1, 1900 6:00 AM

  b) 42005.5 is January 1, 2015 12:00 PM

Build your career as a Data Analyst - sign up for Data Analysis Training using MS Excel!

How to Convert Date to Number in Excel?

 In Excel, if you are wondering which serial number corresponds to the date or time displayed in a cell, then it's possible in two ways.

Format Cells Dialogue

In Excel, right-click the cell with a date and choose Format Cells, as well as, in the General tab, click OK.

If all you want is the knowledge of the serial number given date without actually converting the date to number, then take the number you see written beside Sample and then press Cancel to get the calculation window closed.

If the data found by the formula has to be replaced with the number in a cell, then click on OK. 

Excel DATEVALUE and TIMEVALUE Functionalities

Date value can be computed by entering – =DATEVALUE(“1/1/2015”).

Use the TIMEVALUE() function to get the decimal number representing the time, for example =TIMEVALUE("6:30 AM").

To know both, date and time, concatenate these two functions in the following way:

How to Change Date Format in Excel?  

If you want to change the date format in Excel, you can start by selecting the format you want from the list. The type of format you want to enter is included in the Excel menu.   

Let us learn how to change date format in Excel to YYYY/MM/DD in our example below. 

  1) Choose the cells that require formatting 

  2) Press Command+1 or Ctrl+1  

  3) Click on the “Numbers” option 

  4) Select “date” from the menu 

Click on the “Type” tab; next, choose YYYY/MM/DD or any other format you want 

How to Create a Custom Excel Format?

The list allows you to select the format you want.  You can create a custom format once you’ve chosen the format closest to your requirement. You can change the format as per your requirement by following a few simple steps: 

  1) Select the “Category” option, and choose “custom.”

  2) The format you selected earlier is displayed. Now, you can make changes. These changes will apply to the customised setting only. It will not be included in the default setting.

  3) If you want to enter DD/MM/YYYY in the date display, you’ll have to click on Format Cells. Next, choose the “Custom” option.

  4) Next, fill in DD//MM/YYYY in the given space.

 

How to Change a Date Format to any Other Locale 

People who use dates for multiple locations have the option to switch to a new locale. This can be done by following the below-mentioned steps: 

  1) Choose a particular cell or a group of cells 

  2) Press Command+1 or Ctrl+1 

  3) Click on the “Numbers” tab. Click on “Date” 

  4) Hit on the “Type” option. You’ll find a drop-down menu that displays “Locale” 

  5) Choose the appropriate “Locale” 

If you want to customise these settings for every time you work on excel, follow the given steps: 

  1) Follow the above steps that were used to customise a date 

  2) Come up with an appropriate format 

  3) Include the locale code in the customised format

  4) Select the proper locale codes. The format of locale codes is ($-####).   

Get expertise in visualisation, register in Retired: Analysing and Visualising Data with Microsoft Excel!

Reasons You're Unable to Change Your Date Format in Excel

You can experience several issues when changing the date format. Some of the primary reasons are as follows: 

  1) Sometimes we add dates to Excel as numbers, but sometimes we forget to follow the date format. However, in this case, Excel assumes it to be a text rather than a date.   

  2) The dates in the cell may be left aligned.  

  3) The date entered includes an apostrophe.  

  4) The cell’s size is larger than the standard size. 

  5) Excel formats negative figures as dates. 

Even if the dates are not formatted correctly, you won’t be able to derive the accurate result in your spreadsheet.  

Conclusion 

Many Excel users may find it challenging to change the date format, and Excel may consider dates to be text values. Additionally, others find it difficult to import data from an external database or a .csv file to an Excel spreadsheet. As in this case, Excel considers imported dates as text. We hope you have learnt how to change the date format in Excel using formulas after reading this blog. Note the essential points you must consider when formatting the date. This helps ease your task.   

Learn how to create advanced formulas in MS Excel - Sign up for Microsoft Excel Expert!