How To Change Date Format In Excel?

How To Change Date Format In Excel?

5 mins read264 Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 5, 2024 10:50 IST

Spreadsheets store dates as serial numbers, where each integer represents a single day. When a user enters a date in a cell, it converts to its corresponding serial number. The format is then changed to date. There are different procedures so that we can have a unified format. This article will teach us how to change date format in Excel, using examples to help you understand the concept better.

2022_12_MicrosoftTeams-image-114.jpg

Must Read – What is MS Excel?

Content

  1. Changing Date Formats Manually
  2. Changing System Settings
  3. Highlighting The Relevant Date Fields On Your Spreadsheet
  4. Resize the Cell Size

Explore MS Excel Courses

Changing Date Formats Manually

Writing the date in the required cell is straightforward but not the most recommended one. It is useful when there is little data you want to feed, and if it is common to several cells, you only have to use the copy-and-paste function. To do this:

  • Copy the date into one of the cells and then click the right mouse on the date in question.
  • Select the “copy” option and place the pointer in the cell where you want to paste the data.
  • Click on that cell and select “paste.”
How to Use Excel Filter Shortcut?
How to Use Excel Filter Shortcut?
Excel Filter shortcuts are keyboard combinations that you can use to apply filters to your data quickly and easily. The article will explain different ways to use Excel Filter shortcuts,...read more
150+ Keyboard Shortcuts In Excel
150+ Keyboard Shortcuts In Excel
Excel users who enter a lot of data with the keyboard can boost their productivity by learning Excel shortcut keys, which allow them to access the program’s commands much faster....read more

Changing System Settings

1 To set a different default date and/or time format on your computer, go to the Control Panel and click Region.

Changing System Settings

2 Select – Change the date, time, or number format.

Changing System Settings 2

Must Explore – Free MS Excel Courses

3You would need to select a format to implement the default date and time. There are two options –

  • Short date – Refers to the abbreviated version: e.g. 8/11/2014. 
  • Long date – Refers to the wordier form: e.g. August 11, 2014. 

Whichever format you choose will be standardized across all Windows applications, including Excel. Click “OK” to apply your selection.

4 Review the short date options. We will use August 11, 2014, as an example.

  • M/d/yyyy: 8/11/2014
  • M/d/yy: 8/11/14
  • MM/dd/yy: 08/11/14
  • MM/dd/yyyy: 08/11/2014
  • yy/MM/dd: 15/06/02
  • yyyy-MM-dd: 2014-08-11
  • dd-MMM-yy: 11-Aug-14

Must Read – Most Useful Excel Formulas

Review the long date options. We will use August 11, 2014, as an example.

  • dddd, MMMM dd, yyyy: Monday, August 11, 2014
  • dddd, MMMM d, yyyy: Monday, August 11, 2014
  • MMMM d, yyyy: August 11, 2015
  • dddd, d MMMM, yyyy: Monday, 11 August, 2014
  • d MMMM, yyyy:  11 August, 2014
How to Split Cells in Excel?
How to Split Cells in Excel?
Splitting cells in Excel involves dividing the contents of a single cell into multiple cells. This can be incredibly useful for organizing and formatting data in a way that makes...read more
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel is widely used to generate large numbers of documents simultaneously while sticking to an agreed-upon or required format. The unique information for each card or label...read more

3. Highlighting The Relevant Date Fields On Your Spreadsheet

If dates are aligned in a column

1 – Left-click the top letter. Select and format the entire column.

2 – Right-click for an action menu.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-2.jpg

3 – Click Format Cells to get the date options. You will see the options in the dropdown menu. Choose the one that you prefer.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-3.jpg

You will now see that the date format changes for the entire column.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-4.jpg
4 Ways to TRANSPOSE in Excel – Shiksha Online
4 Ways to TRANSPOSE in Excel – Shiksha Online
Learn how to transpose data in Excel and quickly change the orientation of columns and rows in a spreadsheet.
Difference between Row and Column with Basic to Advanced Examples
Difference between Row and Column with Basic to Advanced Examples
While the difference between row and columns is clear to many, there is still room for doubt when they are used in different contexts. Find them out in this quick-to-read...read more

If dates are aligned in a row

1 – Highlight the section or cell you want to change. 

2 – Left-click on the number at the far left of the row to select all the cells.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-5.jpg

3 – Choose the option you like and click OK.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-6-1.jpg

The date format is changed.

2022_12_Highlighting-The-Relevant-Date-Fields-On-Your-Spreadsheet-7.jpg

Using Format option in Data

1 – Select your data and go to Ribbon.

2 – Hover to Cells ⇒ Format ⇒ Format Cells

Using Format option in Data

3 – You will get the options as previously. Select the one of your choices from the dropdown menu.

Using Format option in Data 2

4. Click OK. Your date format is now changed.

Using Format option in Data 3
How To Add Drop Down In Excel In Less Than 1 Minute?
How To Add Drop Down In Excel In Less Than 1 Minute?
Adding a dropdown list to a cell helps us to allow the user to select data from a list that we have previously predefined. In this way, we maintain consistency...read more
What Are Excel Errors and How To Fix Them?
What Are Excel Errors and How To Fix Them?
Excel is a powerful tool for working with data, but it can also be frustrating when errors occur. While some errors are easy to fix, others can be more difficult...read more

4. Resize the Cell Size

In general, MS Excel understands dates exceptionally well, and you are unlikely to encounter any barriers when working with them. But if you still face the issue where the Excel date format is not working, follow the given troubleshooting tips to eliminate the date format not changing in the Excel issue.

If the cell needs more room to fit the full date and you see multiple pound signs (#####) instead of the dates in your Excel worksheet, then the cells are not comprehensive for the end dates that you enter.

2022_12_Resize-the-Cell-Size.jpg

Double-click the column’s a right edge to correct the date automatically to resize it. Or, to set the width of the column, drag it to the right edge to fix the Excel date format not working problem.

2022_12_Resize-the-Cell-Size-2.jpg

You will see the set date format now.

2022_12_Resize-the-Cell-Size-3.jpg

This method is applicable for date format staying the same in Excel 2007, 2013, and 2016.

We hope this article helped you to understand how to change date format in Excel.


Read More in Excel

MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel

FAQs - How to Change Date Format in Excel?

How can I quickly switch between different date formats?

To quickly switch between different date formats, here are two appoaches to follow -

Keyboard shortcut: Use Ctrl+1 (Windows) or Command+1 (Mac) to open "Format Cells". Press F4 to cycle through recently used formats.

Format Painter:

  • Select a cell with the desired format.
  • Click the "Format Painter" brush on the Home tab.
  • Click the cell(s) you want to change.

Can I create my custom date format?

Yes, you can create your custom date format by selecting "Custom" in the "Format Cells" window and using codes like "d" for day, "m" for month, and "y" for year.

How do I change the default date format for new workbooks?

To change the default date format for new workbooks -

  • Go to Options: Click "File" > "Options."
  • Advanced settings: In the "Options" window, navigate to "Advanced."
  • General options: Scroll down to "General" settings.
  • Choose format: Under "When creating new workbooks," select your preferred default format.

What happens if I change the date format of a cell containing a text string?

Change in the date format of a cell containing a text string may lead to the following - 

  • Excel might interpret the text as a date and display unexpected results.
  • In some cases, you might get error messages if the text cannot be converted to a valid date.
  • Only change the format of cells you know contain actual dates to avoid data corruption.

What are the most common date formats used in Excel

Some of the most common date formats used in Excel are - 

  • DD/MM/YYYY (e.g., 05/02/2024)
  • MM/DD/YYYY (e.g., 02/05/2024)
  • YYYY/MM/DD (e.g., 2024/02/05)
  • Short date (e.g., 5-Feb)
  • Long date (e.g., February 5, 2024)
About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio