PDA

View Full Version : One Cell with different Time and Strings



lks55
03-23-2016, 06:22 AM
Hi everyone,

Im writing a code were I have to read out cells from a sheet and they will be transfered to one cell. The thing is that the content in this cell should be:

Sign in: (time) Sign out: (time)

I want to keep it simple. I have allready written a code but it is really long. Is there a simple possibility to transfer text and time into one cell? Because if i just transfer it like a string the cell looks like:

Sign in: 0.1684654 Sign out: 0.184654 (i know why but maybe there is a function or easy way to display the digits as normal time)

It should look like:

Sign in: 12:30 Sign out: 13:00

I can also put the strings and times in different cells but then my question would be if can I can format a cell out of vba, because in the excel sheet the format of the cells is general.

Thanks a lot

snb
03-23-2016, 06:51 AM
https://www.youtube.com/watch?v=BgBX-cgaiDo

Paul_Hossler
03-23-2016, 07:18 AM
Simple way




Sub test()
Dim s1 As String, s2 As String
Dim v As Variant

s1 = "Sign in: 0.1684654 Sign out: 0.984654"

v = Split(s1, " ")

s2 = "Sign in: " & Format(v(2), "[h]h:mm") & " Sign out: " & Format(v(5), "[h]h:mm")

MsgBox s2
End Sub

lks55
03-23-2016, 08:16 AM
Amazing. Thank you

Paul_Hossler
03-23-2016, 11:58 AM
Be aware that it does make assumptions that the input string is consistently formatted