Formatting Multiple number values in a cell into time values.

[sinneduo]

Hi,
I'm copying multiple time values into a single cell using vba with a space as a separator, however it is resulting in a cell with a bunch of numbers and I am unable to format it back into time.
Snipaste_2023-02-27_09-49-52.pngSnipaste_2023-02-27_09-49-52.png

Is there anyway to convert into time format?

This is the macro I'm using to copy the cells:
where ws1.Cells(y,"E") contains the time values I want to copy

For y = 2 To 3024

If ws1.Cells(y, "A").Value = ws2.Cells(x, "A").Value Then
If ws1.Cells(y, "E") = "6/02/2023" Then
ws3.Cells(x, "B").Value = ws3.Cells(x, "B").Value & " " & ws1.Cells(y, "F")

ElseIf ws1.Cells(y, "E") = "7/02/2023" Then
ws3.Cells(x, "C").Value = ws3.Cells(x, "C").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "8/02/2023" Then
ws3.Cells(x, "D").Value = ws3.Cells(x, "D").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "9/02/2023" Then
ws3.Cells(x, "E").Value = ws3.Cells(x, "E").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "10/02/2023" Then
ws3.Cells(x, "F").Value = ws3.Cells(x, "F").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "11/02/2023" Then
ws3.Cells(x, "G").Value = ws3.Cells(x, "G").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "12/02/2023" Then
ws3.Cells(x, "H").Value = ws3.Cells(x, "H").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "13/02/2023" Then
ws3.Cells(x, "I").Value = ws3.Cells(x, "I").Value & " " & ws1.Cells(y, "F")
ElseIf ws1.Cells(y, "E") = "14/02/2023" Then
ws3.Cells(x, "J").Value = ws3.Cells(x, "J").Value & " " & ws1.Cells(y, "F")
End If
End If

Next y

Next x

Thank your for your assistance.

[TMS]

This is the macro I'm using to copy the cells:

If you concatenate values using ampersand ( & ), then you get Text as your output. In this case, you are concatenating raw numeric time values. You woud need to use Format with each of the time cell values. You will NEVER be able to format the worksheet cell in any way as it is Text.

[TMS]

Note, with 2021, you could probably use FILTER and TEXTJOIN together with TEXT to get the output you require.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[sinneduo]

Thanks TMS, that makes sense.
Just so i'm on the right page, am I formatting the cell before, after or both when copying the values:
ws3.Cells(x, "B").Value = ws3.Cells(x, "B").Value & " " & ws1.Cells(y, "F")
I also assume I would be using something like this to format the cell:
Format(ws3.cells(x,"B", "Long Time")

[sandy666]

attach example excel file not a picture

[TMS]

Assuming ws1.Cells(y, "F") is the cell with the time, then:

Please Login or Register  to view this content.

[sinneduo]

Apologies, I've attached file to main post

[sinneduo]

Hi TMS,
Just tried it and it works! thanks a lot!

[TMS]

I thought you said:

Hi TMS,
I just tried that and it converted all numbers into decimal format.

Whatever … If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[sandy666]

Apologies, I've attached file to main post

........................
err.png