How can I convert date in dd mm yyyy in Excel?

Question

Depending on the country you are in, there are a few different date formats you can use. In this lesson, I’ll show you how to convert any date format to dd/mm/yyyy or DMY in short. It’s the most popular format in the world used by more than half of the world population.

Table of Contents Show

Format Cells
Text function
Download Practice Workbook
4 Methods to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
1. Change the Default Date & Time Format to dd/mm/yyyy hh:mm:ss in Excel
2. Use of the TEXT Function to Convert a Date to dd/mm/yyyy hh: mm:ss Format in Excel
3. Convert a Date to dd/mm/yyyy hh:mm: ss Format Using Custom Formatting in Excel
4. Run a VBA Code to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
Things to Remember
How do I convert date format in Excel?
How do I change my date format from DD MM to YYYY?

We are going to use this example.

The date is formatted to MDY (mm/dd/yyyy).

This is a format used only in the US and a few smaller countries.

Tutorial Content

Format Cells
Text function

Format Cells

The first way, you can convert date is to use the Format Cells window. In order to do it, click cell B3, then click the right mouse button and select the Format Cells option.

From a new window, click Date and select the country where you from.

If you can’t see your format, click the Custom category and type the exact format.

Text function

You can also use the text function to format your date to dd/mm/yyyy.

Let’s modify our example a bit.

Inside C3, insert the following formula.

1

=TEXT(B3,"dd/mm/yyyy")

If you want to keep only the date inside C3, copy this cell and paste it as values. Otherwise, it’ll return an error.

This article illustrates how to convert a date in Excel to dd/mm/yyyy hh:mm:ss format using the TEXT function, Custom Formatting, and VBA code with suitable examples. Excel provides the facility to display dates to the users in different formats. So we need to know the methods to change the date formats to facilitate our work. Let’s dive into the examples to get a clear understanding of the methods.

Table of Contents hide

Download Practice Workbook

4 Methods to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel

1. Change the Default Date & Time Format to dd/mm/yyyy hh:mm:ss in Excel

2. Use of the TEXT Function to Convert a Date to dd/mm/yyyy hh: mm:ss Format in Excel

3. Convert a Date to dd/mm/yyyy hh:mm: ss Format Using Custom Formatting in Excel

4. Run a VBA Code to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Convert Date Format.xlsm

4 Methods to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel

When we put a date in an Excel worksheet, it stores it in a form of sequential serial numbers which start from 1 on 1st Jan 1900. The system adds up the serial number by one for each day onwards. This is useful while performing various calculations with dates. But Excel gives us the flexibility to show a date in different human–readable formats. The following methods will describe how to convert a date to a specific format i.e., dd/mm/yyyy hh:mm:ss format.

1. Change the Default Date & Time Format to dd/mm/yyyy hh:mm:ss in Excel

The moment we type a date in a cell of an Excel worksheet, it stores it in its default format. Here in cell B3, we typed 24 Apr 2021 5:30 PM. Excel stored it as 4/24/2021 5:30:00 PM that is as mm/dd/yyyy hh:mm:ss tt format.

This format comes from the default Date & Time settings of the user’s computer. To check the default format–

Go to the Control Panel.
Click the Change date, time, or number format link under the Clock and Region.

In the Region window, we can see the computer’s default format as English(United States) that uses M/d/yyyy h:mm tt as its date and time format.

Change the Format to dd/mm/yyyy hh:mm:ss:

From the Format dropdown, choose the English(United Kingdom) option.

This region uses our desired format. Now hit OK to make it our default date and time format.

After that, close the Excel Application and re-open it.

The date and time are now in dd/mm/yyyy hh:mm:ss format.

Read More: How to Convert Date to Month and Year in Excel (4 Ways)

2. Use of the TEXT Function to Convert a Date to dd/mm/yyyy hh: mm:ss Format in Excel

Using the TEXT function we can apply a specific format to a date value. The function has two arguments-

=TEXT(value, text_format)

We just need to put the cell reference as the value argument that holds the date and then specify the desired format as the text_format argument.

Here, we have a date that is in m/d/yyyy h:mm:ss AM/PM format in cell B5. Let’s put the following formula in cell C5.

=TEXT(B5,"dd/mm/yyyy hh:mm:ss")

We’ve successfully converted the date and time to dd/mm/yyyy hh:mm: ss format.

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

3. Convert a Date to dd/mm/yyyy hh:mm: ss Format Using Custom Formatting in Excel

We can set custom format code to a cell value in addition to the predefined formats by using the Format Cells options. In this example, we’re going to change the format ( m/d/yyyy h:mm:ss AM/PM) of a date in cell B5 to dd/mm/yyyy hh:mm: ss format. Let’s follow the simple steps to accomplish that.

Select cell B5 that holds the date in m/d/yyyy h:mm:ss AM/PM format.

There are different ways to open the Format Cells window. The easiest way is to press Ctrl + 1 key on your keyboard.
Now in the Format cells window, go to the Number tab.
Then from the Category list, choose the Custom option.
After that, in the Type input box put dd/mm/yyyy hh:mm: ss.
And finally hit OK to save the change.

The date has changed its format to dd/mm/yyyy hh:mm: ss format.

Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)

4. Run a VBA Code to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel

The Range.NumberFormat property in VBA code allows us to set a custom number format to a cell value. In this example, we’ll use this property in our VBA code to change the format ( m/d/yyyy h:mm:ss AM/PM) of the date in cell B5. Follow the steps below to apply the code.

Go to the Developer tab from the Excel Ribbon.
Click the Visual Basic option.

In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now copy and paste the following code in the visual basic editor.

Sub ConvertDateFormat()
Range("B5").NumberFormat = "dd-mm-yyyy hh:mm:ss"
End Sub

Finally, press F5 to run the code and the output is in the following screenshot.

Read More: How to Convert Date to Month in Excel (6 Easy Methods)

Things to Remember

As we have shown in the first example, changing the default date and time configuration of your computer may affect other important settings.

Conclusion

Now, we know how to convert a date to dd/mm/yyyy hh:mm:ss format using 4 different methods. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.