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

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.

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

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.

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.

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

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

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.

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.

Download Practice Workbook

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

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.

Related Articles

• How to Change Default Date Format from US to UK in Excel
• How to Change Date Alignment in Excel
• How to Use Short Date Number Format in Excel

<< Go Back to Date Format | Number Format | Learn Excel