Excel Date Format (Learn and Convert)

In this article, you will find various tasks related to date conversion, formatting, and manipulation in Excel. It also includes converting different formats to date format or dates to number format, converting date format to another locale, creating custom date format and resolving date formatting issues.

We’ll show you different ways including using Format Cells box, Find and Replace, Text to Columns etc. features to change Date format. To change any date to a number you can use Format Cells box or the DATEVALUE function in Excel.

Dates and times are common in Excel, but they can be confusing because Excel can show the same date in different ways. It also stores them internally in a constant format. Knowing about Excel date format will help you to work effectively with Excel.

Click here to enlarge this image.

Download Practice Workbook

Date Format.xlsx

What Is Date Format in Excel?

Excel allows users to customize the date format according to their preferences or requirements. Dates in Excel are stored as serial values, with each date represented by a consecutive number. The default date system in Excel for Windows is 1900, while for Excel on Mac, it is 1904. Understanding these date systems and how Excel stores and recognizes dates is important for accurate calculations and formatting in Excel.

What Are the Different Format Codes for Date in Excel?

Day	Result	Month	Result	Year	Result
d	4	m	2	y	23
dd	04	mm	02	yy	23
ddd	Tue	mmm	Feb	yyy	2023
dddd	Tuesday	mmmm	February	yyyy	2023

How to Change Date Format in Excel?

Here, we have a dataset containing some Festival Names and Dates. However, the Date column is in General format.

Now, we’ll change it to Date format in Excel in 5 different ways.

1. Apply Default Date Format in Excel

To apply the Default date format in Excel, first, select your desired cell range >> go to the Home tab.

Then, click on Number format >> select any of the two default date formats: Short Date or Long Date.

2. Use Format Cells Box to Change Date Format

First, select your data range.
Then to open the Format Cells box, press Ctrl + 1.
After that, go to the Date category >> select any Type given there >> click on OK.

3. Convert Text Values Representing Dates to Actual Dates

Sometimes dates can be inserted as text when you import data in Excel. To convert them to actual dates, you can use the VALUE function.

We used the following formula for our dataset.

=VALUE(C5)

4. Use Find and Replace Feature to Change Date Format

To use the Find and Replace feature to change the Date format, select your data range >> press Ctrl + H.
Then, insert “-” in the Find what box and “/” in the Replace with box >> click on Replace All.

Using Find and Replace Feature to change to Date format in Excel

Click to enlarge the image

5. Change Date Format Using Text to Columns Wizard in Excel

We can also use the Text to Columns wizard to change the Date Format.
For this purpose, select your data range >> go to the Data tab >> click on Text to Columns.

Now, click on Next.

Select any Delimiters available there or check the Other box and insert your desired delimiter >> click on Next.

Select Date as Column data format >> select any format from the drop-down (we’ll select MDY) >> insert a cell reference as Destination >> click on Finish.

Finally, you will find the Date column separated into Month, Day and Year columns.

How to Convert Date Format to Another Locale?

If you want to change the Locale while converting your data format to Date, first open the Format Cells box by clicking Ctrl + 1.
Then, go to the Date Category >> select your desired Locale from the drop-down.
Click OK to proceed.

How to Create Custom Date Format in Excel?

To create a custom date format, open the Format Cells box by clicking Ctrl + 1 >> go to the Custom Category >> insert your desired date code in the Type box ( we’ve inserted ddd mmmm yy) >> click on OK.

How to Convert Date to Number in Excel?

You can convert the Date format to number using Format Cells Box or applying the DATEVALUE function in Excel.

1. Use Format Cells Box to Convert Date to Number

Select your data range >> press Ctrl + 1 to open the Format Cells box >> select General as Category >> click on OK.

2. Convert Date to Number Using Excel DATEVALUE Function

You can also use the DATEVALUE function for this purpose. Remember to insert the date value inside a single (‘’) or double apostrophe (“”). If you insert a cell reference in the function it will return a #Value error.

Here, we used the following formula.

=DATEVALUE("1/1/2023")

How to Fix When Excel Date Format Is Not Working?

Sometimes the Date format may not work in Excel. There are some reasons for that.

Cell is not wide enough: If your cell is not wide enough to show the value in the cell, you will see Hash marks (####) instead of the value. To resize and autofit the cell, double-click on the right border of the cell.
Negative values are formatted as date: If the value of the date is negative, it will also view as Hash marks (####).

To solve this issue, you can switch to the 1904 date system. Go to File >> click on Options >> go to the Advanced tab >> turn on Use 1904 date system >> click on OK.

What Things Should You Remember?

To change values to Date format from any data format, first check which format it is in, then use any of the methods we have provided in this article according to that.

Frequently Asked Questions

1. How to change the date format permanently in Excel?

To change the date format permanently you will have to change the date formats of the Control Panel.

Open Control Panel from the Start menu >> click on Clock and Region >> click on Region >> click on Additional Settings >> go to the Time tab in Customize Format box >> change format according to your need >> click on OK.

2. How to change a date to a text string in Excel?

To change the date to a text string use the TEXT function in Excel. Here we can use the formula below:

=TEXT(date,"yy-mm-dd")

Conclusion

This article provides a comprehensive guide to date formats in Excel, covering various tasks related to date conversion, formatting, and manipulation. Hope you find this helpful and informative. Feel free to comment if something seems difficult to understand. Thank you!

Excel Date Format: Knowledge Hub

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!