##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# Converting separate cells to single cell containing date-time information

0

I have 5 cells containing the Date-Time components (Year, Month, Day, Hour, Minute, Second). How do I convert it to an Excel time format in a single cell?

(The Date-Time information are beeing read from electronic device via communication in separate addresses.)

0
• Select the cell containing the date/time value.
• Right-click and select Format Cells
• On the Number tab select Custom
• In the Type field enter dd/mm/yy hh:mm:ss
You can modify the date format in any way acceptable to Excel, such as m-d-yyyy
• press OK

This presumes that your cell contains a proper date/time value. To check, select Format Cells > Number tab > General. The cell should display a number, like 43907.4112. If it doesn't, changing the cell format to General will have no effect because the value in the cell is a text string. You can convert the text string to a number using the formula below and then format the cell as described above.

``=DATEVALUE(A1) + TIMEVALUE(A1)``

The same method can be used if date and time are in two different cells. If they are proper dates and times, meaning an integer number for date and a decimal fraction for time, just add them, like =A1 + B1. If they are string values convert and add them as shown above, =DATEVALUE(A1) + TIMEVALUE(B1).

EDIT Dec 11, 2019  =======================================

``=DATE(\$B2,\$B3,\$B4)+(\$B5/24)+(\$B6/1440)``

Modified to work horizontally, the formula below will work in your row 10.

``=DATE(\$A10,\$B10,\$C10)+(\$D10/24)+(\$E10/1440)``

Apply the Custom Format below to result cell or another valid date/time format you may prefer as explained in my original answer above.

``mm/dd/yyyy hh:mm``
0

Something doesn't work for me, I have attached file with data validation of the Date&Tima (green cells and I would like to get the full Date&Time selected ib the orange cell.

Could you try setting it?

Note that the reason I did as it is in the attached file is because that is the format I get from an electronic device to the Excel  and I need the conversion to Time stamp

Thanks