This guide will explain how to convert YYYYMMDD to date format in Excel using three easy and simple methods.
Since it has several built-in functions and tools, Excel is popular for different purposes and situations. For example, we can easily perform difficult statistical calculations or efficiently store and organize data in Excel.
When we store and organize data, we usually want to adjust the data format according to the format Excel supports. If we want to input dates in our data set, we need to convert it to Excel date formats. Specifically, we will focus on learning how to convert the YYYYMMDD format to an Excel date format.
Luckily, we can utilize three easy and simple ways to convert the YYYYMMDD format. There are two ways we have inputted the dates in our data set. And these can be in the YYYYMMDD format or as the total count of days since January 1, 1900.
Afterward, we will be utilizing the different built-in functions in Excel to convert the YYYYMMDD format to a date format easily. So we will be creating formulas using the functions to change the formats.
Let’s take a sample scenario wherein we need to convert YYYYMMDD to a date format in Excel.
Suppose you have a data set containing two columns. So the first column has the YYYYMMDD format while the second column has the number format, such as 43566. Since you want to convert the format into an Excel date format, you utilized the TEXT function.
Before we move on to the three easy and simple methods, let’s first understand the different functions we will use.
The Anatomy of the TEXT Function
The syntax or the way we write the TEXT function is as follows:
=TEXT(value, format_text)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- TEXT() is our
TEXTfunction. And this function is used to convert the selected value to a text in a specific number format we chose. - value is a required argument. So this refers to the selected number or formula that will evaluate a numeric value. Additionally, this can also be a cell reference containing a numeric value.
- format_text is another required argument. And this is the number format in a text form from the category box on the Number tab found in the Format Cells window.
The Anatomy of the DATE Function
The syntax or the way we write the DATE function is as follows:
=DATE(year, month, day)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- DATE() refers to our
DATEfunction. And this function is used to return the number that represents the date according to the Excel date-time code. - year is a required argument. So this is the number from 1900 or 1904 to 9999.
- month is also a required argument. And this refers to the number from 1 to 12 according to the month of the year.
- day is another required argument. So this is the number from 1 to 31 representing the day of the month.
Great! Let’s dive into the steps of how to convert YYYYMMDD to a date format in Excel using three simple methods.
How to Convert YYYYMMDD to Date Format in Excel using the TEXT Function
Firstly, we can use the TEXT function to create a formula that will convert the data formatted to the total number of days since the 1st of January 1900.
Essentially, the TEXT function will take the selected value and convert it to the format we have inputted as the second argument, an Excel date format.
To apply this method, simply follow the steps below.
1. Firstly, we will create a new column to input the results. Then, we can type in the formula “=TEXT(C2,”mm/dd/yyyy”)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to apply the formula to the other cells.
3. And tada! We have successfully converted the date.
How to Convert YYYYMMDD to Date Format in Excel using the DATE Function
Secondly, we can create a formula combining the DATE function with the RIGHT, LEFT, and MID functions to convert the YYYYMMDD to a date format in Excel.
So the LEFT, RIGHT, and MID functions will take the selected value as text. Since the selected value is in the format YYYYMMDD, meaning the first four numbers refer to the year, the middle two numbers are the month, and the last two numbers are the date.
Next, the LEFT function will take four numbers, the RIGHT function will take two numbers, and the MID function will take two from where it starts and the character up to which it will take.
To use this method in your work, we can simply follow the steps below.
1. Firstly, we must create a new column to place the output. Then, we can input the formula “=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to copy and apply the formula to the other cells.
3. And tada! We have converted the YYYYMMDD to a date format in Excel.
How to Convert YYYYMMDD to Date Format in Excel using Text to Columns
Lastly, we can use an Excel tool to convert the YYYYMMDD format to a date format in Excel. In this case, we will utilize the Text to Columns tool from the Data tab.
To use this method, we can simply follow the steps below.
1. Firstly, we will select the entire column containing the YYYYMMDD format we want to convert. Then, we will go to the Data tab and click Text to Columns.
2. Once the Convert Text to Columns Wizard is open, we will first select Delimited. Next, we will click Next.
3. Afterward, we will not check any boxes under the Delimiters and select Next.
4. Then, we will choose Date and click YMD from the dropdown menu. Lastly, we will click Finish to apply all the changes.
5. And tada! We have successfully converted the date format.
You can make your own copy of the spreadsheet above using the link attached below.
That’s pretty much it! We have explained how to convert YYYYMMDD to date format in Excel using three simple and easy methods. Now you can simply choose any of the methods and apply them to your work whenever you need to convert your data to Excel date formats.
Are you interested in learning more about what Excel can do? You can now use the DATE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
