Date and Time Format for range in Excel VBA

Hi all.

I have the following code that formats some of the columns in my Excel worksheet:

Dim rng2 As Long

With Worksheets("Sheet1")

    rng2 = .Range("A" & Rows.Count).End(xlUp).Row
.Range("N2:N" & rng2).Style = "Currency"

.Range("A1:R" & rng2).Columns.AutoFit
End With

Open in new window

Now I want to format column F to show the date and time in the following format: mm/dd/yyyy hh:mm AM/PM

But I'm not able to do so, here's what I've tried so far:

.Range("F2:F" & rng2).NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
.Range("F2:F" & rng2) = Format("mm/dd/yyyy hh:mm AM/PM")

Any ideas?

Thank you in advance.
Who is Participating?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The first one should work. What is the result? You can always try in the VBA Editor's Direct Window, e.g. with [A1].NumberFormat, for testing.
Saqib Husain, SyedEngineerCommented:
What happens with the first option?
printmediaAuthor Commented:
You're right it does work, the problem was the column was not wide enough to see the time portion it was just displaying the date. Thanks!
Anthony BerenguelCommented:
in testing
.Range("F2:F" & rng2).NumberFormat = "mm/dd/yyyy hh:mm AM/PM"

Open in new window

worked for me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.