Still Struggling with VBA Formatting Of Values In Cells

[Ark68]

I have this line of code:

.Cells(drow, 14) = Format(Application.WorksheetFunction.VLookup(luVal, .Range("U3:X60"), 3, False), "h:mm AM/PM dd-mmm")

The value found with the vlookup is 44764.29167, representing a date/time of July22, 2022 7:00 AM.
When I apply this formatting to cell(drow,14), the result is 2022-07-22, not 7:00 AM 22-Jul as desired.

Thoughts?

 

[6StringJazzer]

You are not applying formatting. The function Format creates a text string, it does not format the cell. You are jamming the resulting string "7:00 AM 22-Jul" into the cell. Excel sees this text string as a date/time and since the year is missing it assumes the current year.

You need to do this:

.Cells(drow, 14) = Application.WorksheetFunction.VLookup(luVal, .Range("U3:X60"), 3, False)
.Cells(drow, 14).NumberFormat = "h:mm AM/PM dd-mmm"

 

[Ark68]

Thank you Jeff ... I certainly did not know that about the "Format" function. My lack of understanding was indeed the problem.
Just out of curiosity, when might one want to use "Format"?

 

[6StringJazzer]

You would use Format whenever you need a text string, such as showing a date/time in a text box.