Knowing how to manage dates in Excel is a valuable skill. This guide shows you how to manage different formats of dates in your Excel sheet.
I will use the Office 365 version of Excel (build 2203) for this guide. However, the instructions will be the same for other versions.
Managing Date Formats in Excel
Around the world, different formats are used to write dates. Excel can usually recognize when someone enters a date in a frequently used format.
However, if Excel is unable to recognize the entered information as a date or if you wish to change to a custom format, you can follow these steps:
- Enter the date in your desired format.
- Check the Number section in the Home tab. If Excel recognizes the entered information as a date, it will be formatted as a date in the box.
In this case, Excel could not recognize the information correctly, so it is formatted as General.
To use different functions available with the date format, you will need to change the cell formatting from General to Date.
- To change the cell formatting to Date, open the Number Format drop-down menu.
This menu contains two date formats. The Short date format shows dates in numbered form. In a Long date format, the Month is written in text form.
- To format your cell to a short date, select the Short Date option from the menu.
- If the entered date contains text, you should use the Long Date format to display it in text form.
After formatting the cell as a Long Date, it is displayed as follows:
Using Custom Formatting for Dates
Excel also has the option to use custom date formatting. This allows you to select the exact date format you’re looking for.
Here’s how you can apply custom formatting for dates in Excel.
- Select the cell that contains the date. In the Home tab, navigate to the Cells section and open the Format drop-down menu.
This menu contains multiple formatting options for your Excel sheet. To format the data type entered in your selected cell, open the Format cells menu.
- In the Category section, select Date.
- The Type section contains multiple date formats that Excel recognizes. You can also open the Locale drop-down menu to view date formats used in your selected location.
- Select your desired format and click OK to apply the date formatting.
Your custom formatting is complete.