Excel VBA Course
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.)

View Course

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

Answer
Discuss

Answers

0
Selected Answer
  • 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  =======================================

Please enter the formula below in your test sheet's cell B1.

=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
Discuss
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

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login