How to Use Formula to Change Date Format in Excel (5 Methods)

In this article, we’re going to show you 5 methods of how to change Date format in Excel Using formula. Our sample dataset contains data of 6 people. It has 3 columns: Name, DOB, and Format. Our aim is to change the format using the Excel formula.

Formula to Change Date Format in Excel


Download Practice Workbook


5 Ways to Change Date Format in Excel Using Formula

1. Change Date Format in Excel Using the TEXT Function

In this method, we’ll use the TEXT function to format Date in Excel. We’re going to format each cell in a unique format. Let’s jump into the action.

Steps:

  • Firstly, type the following formula in cell D5.
=TEXT(C5,"mm/dd/yyyy")

Here, we’re converting the date value into a text value using the TEXT function. For the format, we’re providing the “mm/dd/yyyy” part. That is, two-digit for months, dates, and four-digit for years.

Formula to Change Date Format in Excel

  • Finally, press ENTER.

Our date will convert to our predefined format.

Now, we can format the cells in other ways too. The formula is shown on the right side for each of the date formats.

Formula to Change Date Format in Excel

Read More: How to Convert Text to Date with Excel VBA (5 Ways)


2. Applying Combined Functions to Change Date Format in Excel

We’re going to use the DATE function, the LEFT function, the MID function, and the RIGHT function to change the date format in Excel in this method. Notice, our first two values are in number format (right alignment) and the other four values are in text format (left alignment).

At first, we’re going to change the date format for the first two values.

Steps:

  • Type the following formula in cell D5.
=DATE(LEFT(C5,4),MID(C5,5,2),RIGHT(C5,2))

Formula to Change Date Format in Excel

  • Secondly, press ENTER.

The LEFT, MID, and RIGHT functions extract data from a cell. Our initial cell value was 19820321. Outputs will be like these:

  • LEFT(C5,4) >>> 1982 [First 4 values from left side].
  • MID(C5,5,2) >>> 03 [First 2 values from position 5].
  • RIGHT(C5,2) >>> 21 [First 2 values from right side].

Then, our formula will become, DATE(1982,03,21). The format is DATE(YEAR,MONTH,DATE). Thus, we have changed our date into another format.

Now, we’ll format the next 4 cells.

Formula to Change Date Format in Excel

  • Firstly, type the formula from below to cell D7.
=DATE(RIGHT(C7,4),MID(C7,4,2),LEFT(C7,2))

  • Secondly, press ENTER.

Our, formula is slightly changed in this case. Notice the original date format, “dd.mm.yyyy”. If you didn’t understand, this formula was explained above, please go through it again.

Formula to Change Date Format in Excel

In conclusion, we’ve changed the date formats using a formula. This is what the final step should look like.

Related Content: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)


3. Utilizing the SUBSTITUTE and DATE Function to Change Date Format in Excel

The SUBSTITUTE function and the DATE function will be used here to change the date format. Our dates are in text format.

Formula to Change Date Format in Excel

Steps:

  • Type the following formula in cell D5.
=DATE(YEAR(SUBSTITUTE(C5,".","-")),MONTH(SUBSTITUTE(C5,".","-")),DAY(SUBSTITUTE(C5,".","-")))

Notice, we’ve SUBSTITUTE(C5,”.”,”-“) three times in our formula. This is used to replace dot.” with a dash”. In order to make the text format into a date format, this needs to be done. The DATE function will convert it into the date format, else we may get serial numbers instead.

  • Secondly, press ENTER.
  • Finally, use the Fill Handle to apply the formula to other cells.

Formula to Change Date Format in Excel

Thus, we change the date format using the SUBSTITUTE and DATE functions.

Related Content: How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel


Similar Readings:


4. Change Date Format in Excel by Applying the CONCATENATE and DAY Functions

We’re going to use the CONCATENATE function, the DAY function, the MONTH function, and the YEAR function to change the date format. Let’s start.

Formula to Change Date Format in Excel

Steps:

  • Firstly, type the formula from below in cell D5.
=CONCATENATE(DAY(C5),"/",MONTH(C5),"/",YEAR(C5))

In this case, we’re taking the day, month, and year values from cell C5 and attaching them with a forward slash “/” to change our date format.

  • Secondly, press ENTER.

Our date will be changed into “dd/mm/yyyyformat.

  • Finally, use Fill Handle to use that formula up to cell D10.

Formula to Change Date Format in Excel

So, we’ve used the CONCATENATE function to change the date formats.

Read More: Excel Formula for Current Month and Year (3 Examples)


5. Using the Ampersand and DAY Function to Change Date Format in Excel

In this case, we’re going to use the Ampersand and the DAY, MONTH, YEAR functions to change the date format in Excel. Notice, this time, we’ve our dates in serial number format.

Formula to Change Date Format in Excel

Steps:

  • Firstly, type the following formula in cell D5.
=MONTH(C5)&"."&DAY(C5)&"."&YEAR(C5)

We’re joining the cells with a dot (“.”) using the Ampersand. Moreover, we’ve chosen to keep the date format in “m.d.yyyy” as a text format (left alignment).

  • Secondly, press ENTER.
  • Finally, use the Fill Handle to use the formula in the below cells.

Formula to Change Date Format in Excel

Our goal of changing the date format using a formula is complete.

Read More: How to Convert Date to Day of Week in Excel (8 Methods)


Practice Section

We’ve provided a practice dataset for your convenience in the Excel file. You can try out those and follow along with our step-by-step guide.

Formula to Change Date Format in Excel


Conclusion

We’ve shown you 5 methods to change the date format in Excel using formula. If you face any problems regarding this article, feel free to comment below. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo