VBA Date & Time in Same cell but different lines

[jjlafond]

Hello,

Right now I have a macro set up to enter a time-stamp every time the macro runs. It enters it all as one line, and wrap text is not what I want to do (unless there is NO alternative).

What I want to do is convert this:

[TABLE="width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]more dates[/TD]
[TD]8/6/2014 12:26:04 PM[/TD]
[TD]more dates[/TD]
[/TR]
[TR]
[TD]data...[/TD]
[TD][/TD]
[TD]data...[/TD]
[/TR]
</tbody>[/TABLE]

into this (with a Carriage return, or new line, or whatever its called between the data and time):
[TABLE="width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]more dates[/TD]
[TD]8/6/2014
12:26:04 PM[/TD]
[TD]more dates[/TD]
[/TR]
[TR]
[TD]data...[/TD]
[TD][/TD]
[TD]data...[/TD]
[/TR]
</tbody>[/TABLE]

The line of code I am using now to get the top time-stamp is this:

Range("A24").End(xlToRight).Select
ActiveCell.Offset(-6, 1).Select
ActiveCell.Offset(-2, 0).Value = Now

Does anyone know a way to do this?

 

[MickG]

Something like this :-

Dim dt As Variant
dt = Split(Now, " ")
[a1] = dt(0) & Chr(10) & Format(dt(1), "hh:mm AM/PM")

 

[Rick Rothstein]

What I want to do is convert this:

[TABLE="width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]more dates[/TD]
[TD]8/6/2014 12:26:04 PM[/TD]
[TD]more dates[/TD]
[/TR]
[TR]
[TD]data...[/TD]
[TD][/TD]
[TD]data...[/TD]
[/TR]
</tbody>[/TABLE]

into this (with a Carriage return, or new line, or whatever its called between the data and time):
[TABLE="width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]more dates[/TD]
[TD]8/6/2014
12:26:04 PM[/TD]
[TD]more dates[/TD]
[/TR]
[TR]
[TD]data...[/TD]
[TD][/TD]
[TD]data...[/TD]
[/TR]
</tbody>[/TABLE]

The line of code I am using now to get the top time-stamp is this:

Range("A24").End(xlToRight).Select
ActiveCell.Offset(-6, 1).Select
ActiveCell.Offset(-2, 0).Value = Now

Does anyone know a way to do this?

Well, I really cannot figure out what you are doing to lock onto the cell with the date (starting at cell A24 and looking right until you find a cell with data, then moving up 6 and over 1 from it only to move up 2 more cells afterward?), so let's just say you have the cell with the date already in it selected, then try this...

Selection.Replace "  ", vbLf, xlPart  [COLOR=#008000]'There are two spaces between those quote marks[/COLOR]
Selection.EntireRow.AutoFit   [COLOR=#008000]'I included this line so you could see both lines after they were split apart[/COLOR]

EDIT NOTE: I just noticed that this message was my 15,000th posting to this forum!

 

[Rick Rothstein]

Something like this :-

Dim dt As Variant
dt = Split(Now, " ")
[a1] = dt(0) & Chr(10) & Format(dt(1), "hh:mm AM/PM")

You can do that in one line of code...

[A1] = Join(Split(Now, " ", 2), vbLf)

 

[jjlafond]

@ MickG:
I'm trying that, but I think the Split function is recognizing the word "Now" and not the string of numbers/characters at this point. It works fine for dt(0), but comes up with an error that I narrowed to to dt(1) is out of range.
Any ideas on how to correct that?


@ Rick Rothstein
I tried yours and did get it to work, but I cannot change the date and time format for the cell or row being time-stamped (perhaps because it was entered using Now?).
Any ideas on how to change the format (I would like to get the time to 24 hour format without seconds), using vba?

If only I could figure out how to combine both codes.

 

[jjlafond]

I finally figured out how to combine them:

I discovered that Split(Now, "[1space]") splits into 3, but Split(Now, "[2space]") splits into 1.

I modified the above suggestions to create this:

Dim dt() As String
dt() = Split(Now, " ", 2)
ActiveCell.Offset(-2, 0).Value = dt(0) & Chr(10) & Format(dt(1), "hh:mm")

which spits out:
8/6/2014
14:10:07

@ Rick Rothstein

Is there a way to take what I have in bold, and turn it into 1 line using something similar to your 1-lline suggestion?


[A1] = Join(Split(Now, " ", 2), vbLf) 'Modified to include the above formatting change
</pre>

 

[Rick Rothstein]

@ Rick Rothstein

I tried yours and did get it to work, but I cannot change the date and time format for the cell or row being time-stamped (perhaps because it was entered using Now?).
Any ideas on how to change the format (I would like to get the time to 24 hour format without seconds), using vba?

It is not clear to me, but do you want the cell to still be a Date but with the date and time parts on separate lines within the cell? If so, give this a try...

Selection.NumberFormat = "m/d/yyyy" & vbLf & "h:mm AM/PM"
Selection.WrapText = True

 

[jjlafond]

It is not clear to me, but do you want the cell to still be a Date but with the date and time parts on separate lines within the cell? If so, give this a try...

That is correct, except I would like to have "hh:mm: format (24-hour instead of AM/PM)

I tried the lines of code you suggested, and modified them to be:
Selection.NumberFormat - "m/d/yyyy & vbLf & "hh:mm"

But here's the issue.
I checked the individual cell and it DID switch to this custom formatting. However, the date/time that is in the cell did NOT convert to the new format (It is on 2 lines, but it is still displayed in am/pm).

Any suggestions on how to actually make the date/time conform to the commanded format of the cell?

 

[Rick Rothstein]

That is correct, except I would like to have "hh:mm: format (24-hour instead of AM/PM)

I tried the lines of code you suggested, and modified them to be:
Selection.NumberFormat - "m/d/yyyy & vbLf & "hh:mm"

But here's the issue.
I checked the individual cell and it DID switch to this custom formatting. However, the date/time that is in the cell did NOT convert to the new format (It is on 2 lines, but it is still displayed in am/pm).

When I execute that line of code, the time changes to a 24-hour format, so I cannot make happen what you are saying is happening for you.

 

[jjlafond]

Huh, I don't understand why it won't work.

Regardless, I do have a working solution. I am just trying to learn more and make the code more streamlined. Thank you for your help!