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