Menu

How To Change the Date Format in Excel

Ask a question

This article will teach you how to change the date format in Microsoft Excel, allowing you to have a uniform format across your spreadsheet.

Unable to Change Date Format in Excel

Microsoft Excel offers its users various formats for writing dates, including DD/MM/YYYY, DD/MM/YY, MM/DD/YY, and date custom formats. Unfortunately, if dates are formatted differently within a spreadsheet, the system does not always recognize when a cell contains a date. This oversight can easily lead to confusion when dates are formatted differently across a spreadsheet. Luckily, there is an easy way to format your spreadsheet so that each piece of data input into your spreadsheet reads the same.

Open your Excel spreadsheet and select the column containing the dates. Go to Data and choose Text to Column . Choose delimited and then click on Next. Uncheck all delimiters and click Next. Finally, click on date, choose the format in which you'd like to read your dates (the default format is D/M/Y), and then click Finish:


Modify Date Formats in Excel

If you have entered dates into your Excel worksheet and they are not in uniform format, you can modify the properties of the cell, column, or row so that all dates appear in the same format, i.e. DD/MM/YYYY or DD/MM/YY.

To do so go to you Excel spreadsheet, select the fields you would like to modify, then right-click, and select Format Cells...:


In the window that opens, select Date in the Categories list. In the Type list you can select your desired format and click OK to apply the changes:


Image: © Microsoft.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Related