issue displaying date with applied format

[Bill_Davenport]

Hello,

I currently have a script dumping information into an email however the date format in my spreadsheet is too long and i'd like to cut it down to just display the Hour currently the date is formatted like this:

01/08/2017 8:00:00 AM

and I would prefer to just show 8 (8am basically) and dump it into an email however when i do this the date format is getting messed up and it shows up weird.

this is the code i'm using to format the date to only show the hour, which works in excel.

Selection.NumberFormat = "h"

this is the code i'm using to dump it into an email

With OutMail
        .To = distgrp
        .CC = cclist
        .BCC = ""
        .Subject = "*URGENT* " & Range("H" & i).Value & " - Not ready " & Range("F" & i).Value & " to " & Range("G" & i).Value & ". Order# " & Range("B" & i).Value
        .HTMLBody = strbody
        .Display
    End With

when i run this code the subject line is showing up *URGENT* North - Not ready 42948.3333333333

any suggestions on how i can have this display just the hour instead of 42948.3333?

thanks!

 

[John_w]

Use the VBA Format function:

Format(Range("F" & i).Value, "h")