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

Get FREE Advanced Excel Exercises with Solutions!

In this article, we’re going to show you 5 methods of how to use formula to change date format in Excel. 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.

Table of Contents hide

How to Use Formula to Change Date Format in Excel: 5 Methods

1. Change Date Format in Excel Using the TEXT Function

2. Applying Combined Functions to Change Date Format in Excel

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

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

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

Practice Section

Conclusion

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:

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.

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.

2. Applying Combined Functions to Change Date Format in Excel

We’re going to use DATE, LEFT, MID, and RIGHT functions to change the date format in Excel in this method. Notice, that 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))

Then, 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,DAY). Thus, we have changed our date into another format.

Use Fill Handle to AutoFill the formula into cell D6.

Now, we’ll format the next 4 cells.

First, type the formula from below to cell D7.

=DATE(RIGHT(C7,4),MID(C7,4,2),LEFT(C7,2))

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.

Finally, use the Fill Handle to AutoFill the formula down.

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

Related Content: How to Convert Date to Number in Excel

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

A combination of SUBSTITUTE and DATE functions will be used here to change the date format. Our dates are in text format.

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 replaces the dot “.” with a dash “–”, in order to make the text format into a date format. The DATE function will convert it into the date format. Otherwise, we may get serial numbers instead.

Press ENTER.
Finally, use the Fill Handle to apply the formula to other cells.

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

Similar Readings:

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

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

Steps:

First, 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.

Then, press ENTER.

Our date will be changed to “dd/mm/yyyy” format.

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

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

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.

Steps:

First, 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).

Press ENTER.
Finally, use the Fill Handle to use the formula in the below cells.

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

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.

Download Practice Workbook

Formula to Change Date Format.xlsx

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!

2 Comments

lokesh Apr 15, 2023 at 4:06 PM

Hi,

Need Help Here Pls
I have different date format as below in same column,
Need formula to change as DD MMM YYYY
Query Need result as
02/09/2010 02 Sep 2010
11/03/2023 11 Mar 2023
16/03/2023 16 Mar 2023
40423 04 Apr 2023
4042023 04 Apr 2023
20230404 04 Apr 2023
- Reply
  Aniruddah Alam Apr 18, 2023 at 10:53 AM
  Thanks, LOKESH for your query. In Excel, if you want to convert different date formats into a specific date format with a single formula, the formula will be rather long and complicated. However, you can create a custom function using the following VBA code to do your task.
  
  Code Syntax:
```
Function FormatDate(cell As Range)
Value = cell.Value
If IsDate(Value) Then
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 5 Then
    Value = DateSerial(Right(Value, 2) + 2000, Mid(Value, 2, 2), Left(Value, 1))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 6 Then
    Value = DateSerial(Right(Value, 2) + 2000, Mid(Value, 3, 2), Left(Value, 2))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 7 Then
    Value = DateSerial(Right(Value, 4), Mid(Value, 2, 2), Left(Value, 1))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 8 Then
    x = DateSerial(Right(Value, 4), Mid(Value, 3, 2), Left(Value, 2))
    If Year(x) < 1900 Then
        x = DateSerial(Left(Value, 4), Mid(Value, 5, 2), Right(Value, 2))
    End If
    FormatDate = Format(x, "dd mmm yyyy")
Else
    FormatDate = "Format Not recognised"
End If
End Function
```
  Here, I have created a Custom Function named FormatDate. Then, I apply the function to different date formats that you provided. Here is the result.
  
  Hopefully, it will solve your problem. If you face problems anymore, feel free to post them on our Exceldemy Forum.
  Regards
  Aniruddah

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