Excel boasts powerful tools for data organization and analysis. You can manage any type of data, including text strings, numbers, and dates. However, new users may get confused when working with dates because they can be displayed in many different formats in Excel.
But don’t worry because you can easily change the format of the dates to suit your needs.
This is a quick guide on Excel date formatting – how to change date format in Excel. Additionally, we will dive deep into tips on identifying valid date formats, changing the default date format to specific countries, and much more.
So, make sure to read the article till the end.
You can click on the following links to jump to the particular section of your interest:
- Date formatting in Excel
- Setup custom date format
- Change the default date format to a specific country
Download the Example Excel
Before we proceed further in the article, make sure to download the Excel containing the datasets used to demonstrate the date formatting.
Follow me along and practice the things we will discuss. It will help you strengthen the skills required to handle dates like a pro.
If you have your datasheet ready with the required dataset, then skip downloading the above file.
How to Format Dates in Excel
Formatting dates in Excel allows you to present them in a way that suits your needs, making your data easier to interpret.
Note that Excel stores dates as sequential numbers, and the way these numbers are displayed as dates, times, or both depends solely on the cell’s formatting.
You can follow any of the methods mentioned below to change the format of the dates.
METHOD #1: Right-click Method
This method is pretty straightforward. It is the quickest method to display dates in a variety of formats as per your requirements.
Let’s consider the example below, which includes three different dates. Our task is to convert the dates in A2, A3, and A4 to the “DD/MMMM/YYYY” format.
Let’s begin,
- Select all the cells with dates you want to format
- Right-click on the selected cells
- Choose the “Format Cells” option from the popup
(You can also use the keyboard shortcut “Ctrl + 1” to open the “Format Cells” dialog box) - A new popup will appear on your screen, as shown below
- In the newly opened Format Cells dialog box, click on the “Number” tab
- From the category list on the left, select the “Date” option
- Once you click on “Date”, you will see a list of date formats. Select the format that matches your preference
- After selecting your desired format, click on “OK” to apply the changes
Excel will instantly convert the dates to the date format you have chosen, as shown in the GIF above.
Excel offers a range of predefined date formats. But what if you need a custom date format?
You need to click on the Custom option from the category list, as shown in the following image.
METHOD #2 – Ribbon or Main Menu Method
There is one more method you can use to change the formatting of the cells to the date. Compared to the previous method, here you need to use the “Home” tab from the main menu.
Here are the steps,
- Select all the cells containing the dates
- Go to the “Home” tab on the ribbon
- Find the “Number” section in the following image. You need to click on the dropdown
- Next, select the “More Number Formats” option. Refer to the following image,
- In the “Format Cells” dialog box, locate the “Number” tab at the top, and on the left side, under “Category”, select the “Date” option
- You can choose the date format you prefer from the list
- Once you finish, click on “OK” to apply the changes
The exact process is used to convert an integer value to a date. Microsoft Excel has a vast number of predefined date formats. If you are not happy with them, then please refer to the next section of the article.
How to set Custom Date Format in Excel
Sometimes, as discussed in the previous section, you may want to change your date to a format that is not specified in the list.
Let us consider the example dates we used previously. Our task is to convert the dates in A2, A3, and A4 to the “MMMM/DD/YYYY” format.
Here are the steps,
- Start by selecting all the cells containing the dates
- Right-click on the selected cells
- Click on the “Format Cells” option from the list
- A “Format Cells” dialog box will be opened, as shown below
(You can use the keyboard shortcut “CTRL+ 1” to open the “Format cells” dialog box) - Click on the “Number” tab
- Click on “Custom” from the list of categories on the left
- In the “Type” box, enter the custom date format as “MMMM/DD/YYYY” as shown below
- Click on “OK” to apply the changes
Note that you can use the above steps to format any number of dates written following multiple formatting. All you need to do is select all the cells, including numbers.
How to change the default date format to the United Kingdom
To change Excel’s default date format to the United Kingdom (UK), you need to adjust your computer’s regional settings. This will ensure that Excel uses the UK date format by default.
Follow the steps below to change the default date format to the United Kingdom.
- Press the “Windows +R” keyboard shortcut on your computer
- Type “Control Panel”
- Press “Enter” on your keyboard or the “OK” button as shown below
- Next, select the “Clock and Region” option, as shown in the following image
- Click on “Region” or “Change date, time, or number formats”
- In the Region dialog box, under the “Formats” tab, you will see a dropdown menu as shown below
- Select “English (United Kingdom)” from the dropdown menu
- You can click on “Additional Settings” to further customize the date format
- Go to the “Date” tab and adjust the “Short date” and “Long date” formats if needed
- Click on “Apply” and then “OK” to save your changes
Note that you need to close and reopen Excel to make sure the settings are applied.
How to validate a date format in Excel
Note that all of the above steps to change the date format are applicable to valid dates in Excel.
Consider the following example,
In the above image, not all of the dates are valid and acceptable by Excel. So, how would you check if a date is valid?
There are three ways to do so.
METHOD #1 – Check the alignment of the Date
Valid dates are always aligned to the right side of the cell, as shown in the image below.
Here, the dates in the first three cells are valid, whereas those in the fourth and fifth rows are invalid.
If Excel does not recognize the date formats, they are shifted to the left side of the cell and treated as a regular text string.
METHOD #2 – Using the DATEVALUE Function
The DATEVALUE is one of the simple but most useful functions in Excel. The general syntax of this formula is as follows,
=DATEVALUE(date_string)Here, the “date_string” is the date in text format or the cell reference containing the date string that you want to convert.
Here is an example of the DATEVALUE function,
As the first three dates are valid, the DATEVALUE function successfully returned serial numbers for them.
On the other hand, the dates in cells B5 and B6 are invalid, so the function has returned the errors as shown below.
Method #3 – Using the “ISNUMBER” Function
You can use the ISNUMBER function to check if the values in cells represent valid dates.
The ISNUMBER function in Excel helps users determine whether a cell contains a numeric value, which can include dates since Excel stores dates as serial numbers.
The general syntax for the ISNUMBER function is straightforward:
=ISNUMBER(value)Here, the “value” argument represents the value you want to test. It can be a cell reference, formula, or direct input.
Here is an example of the ISNUMBER function,
As the first two dates are valid, the ISNUMBER function successfully returns the output “TRUE”, representing a valid date.
On the other hand, the dates in the third cell return a FALSE, representing an invalid date.
To Summarize: Excel Date Formatting
Changing date formats in Excel allows others to gain insights quickly from the given data. Anyone can easily format dates using the right-click or main menu method.
If you are not happy with the predefined formats, setting a custom date format is also easy.
Feel free to comment below if you are stuck somewhere or having any particular issue. Our team is always happy to assist you.
Also, make sure to explore our blog section, which is full of valuable tips and tricks for using Microsoft Excel and Google Sheets like a pro.