Auto Date & Time

[Keyrookie]

Hey all,

I have this code (which I got from this forum... thank you!) in a
worksheet but I need a format code as well.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A" & Target.Row).Value = Date & Time
Application.EnableEvents = True
End Sub


Excel is returning this format:

11/21/20089:10:55 AM (This is correct except it's formatted wrong)

I've tried formating the cells to: m/d/yy h:mm but I still get the
above format. The only thing I can conclude is that I must have a
format command in the code as well.

Please help!

 

[Per Jessen]

Hello

Try this:

Range("A" & Target.Row).Value = Format(Date & " " & Time, "m/d/yy h:mm")

Regards,
Per

 

[Rick Rothstein]

You are assigning...

Date & Time

in the 2nd line of the subroutine. This butts up the date next to the time
with nothing between them... Excel won't see that as a date/time
combination. You need a space between them. Try assigning this instead...

Date & " " & Time

I think you will then be able to format the cell any way that you want.

 

[Keyrookie]

Thanks Per, that did the trick! Also, for anyone else looking for thi
result, cells must be custom formatted to "m/d/yy h:mm AM/PM
otherwise Execl will return military time.

Thanks again for the quick response,

K