Cell contents appearing as a decimal not time

[andrewb90]

Hello all,

I am trying to send cell contents to an email, but time values in my cells are converting to decimals. Is there a simple way to make it show the time properly? I thought .value would do it, but it didn't make a difference.

Else  Gmail "z@gmail.com", "zzz", "", _
    "o> " & vbNewLine & Sheets("Print").Range("AH4") & vbNewLine & Sheets("Print").Range("AH5") & vbNewLine & Sheets("Print").Range("AH6") & "  " & Sheets("Print").Range("AI6") & "  " & Sheets("Print").Range("AJ6").Value & "  " & Sheets("Print").Range("AK6") & "  " & Sheets("Print").Range("AL6").Value & "", _
    sTo, _
    "1@2.3", _
    pdf

 

[marcelocbd]

Try this:

CDate(Sheets("Print").Range("AL6").Value)

 

[andrewb90]

When I put in CDate, I get a type mismatch error.

 

[marcelocbd]

When I put in CDate, I get a type mismatch error.

Ohh, you want time, not date. My bad

How is your time stored in the worksheet? As string, number, etc?

 

[andrewb90]

The cell is formatted as a time (13:58PM)

 

[marcelocbd]

Try putting:

Format(Sheets("Print").Range("AL6").Value, HH:nn)

 

[andrewb90]

I keep getting errors. its having an issue with the :

 

[marcelocbd]

I keep getting errors. its having an issue with the :

I forgot the quotation marks

Format(Sheets("Print").Range("AL6").Value, "HH:nn")

 

[andrewb90]

So that works, but it isn't showing AM/PM. How would I do that?

 

[marcelocbd]

So that works, but it isn't showing AM/PM. How would I do that?

You use as na example 13:48, so I assumed you used 24-hour format, which doesn't need AM/PM, but if you want the am/pm to be displayed you can just add am/pm in the format function, like

Format(Sheets("Print").Range("AL6").Value, "HH:nn AM/PM")

Plus, you can format the am/pm in the following 5 diferente ways, just use the upper or lower case options below:


AM/PM

Use the 12-hour clock and display an uppercase AM with any hour before noon; display an uppercase PM with any hour between noon and 11:59 P.M.

am/pm

Use the 12-hour clock and display a lowercase AM with any hour before noon; display a lowercase PM with any hour between noon and 11:59 P.M.

A/P

Use the 12-hour clock and display an uppercase A with any hour before noon; display an uppercase P with any hour between noon and 11:59 P.M.

a/p

Use the 12-hour clock and display a lowercase A with any hour before noon; display a lowercase P with any hour between noon and 11:59 P.M.

AMPM
Use the 12-hour clock and display the AM string literal as defined by your system with any hour before noon; display the PM string literal as defined by your system with any hour between noon and 11:59 P.M. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as defined by your system settings. The default format is AM/PM.