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.


Download Practice Workbook

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


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 humanreadable 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.

Excel Date Format dd mm yyyy hh mm ss

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.

Excel Date Format dd mm yyyy hh mm ss

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")

Excel Date Format 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)


Similar Readings:


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.

Excel Date Format dd mm yyyy hh mm ss

  • 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.

Excel Date Format dd mm yyyy hh mm ss

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

Excel Date Format dd mm yyyy hh mm ss

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.

Excel Date Format dd mm yyyy hh mm ss

  • 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

Excel Date Format dd mm yyyy hh mm ss

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

Excel Date Format dd mm yyyy hh mm ss

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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo