Excel VBA Format cell as custom "h"

Question

I have a cell with a time value as 1:23:45 AM and would like to display just the hour value or use it for other calculations. Need to do this using vba. No, I cannot format the column manually.

Thanks, Sam

Answer 1

I am not sure whether your cell is a time field or not. If it is, can you do the following?

dim h as integer
h = Hour(Cell(1,1))

Answer 2

To use VBA to format the cell to only display the hour:

With Worksheets("Sheet1").Cells(1, 1)
    .NumberFormat = "H"
End With

To read the hour value in VBA (regardless of how the cell is formatted):

Dim hourValue As Integer

With Worksheets("Sheet1").Cells(1, 1)
    If (IsDate(Format(.Value, "hh:mm:ss"))) Then
        hourValue = Hour(.Value)
    Else
        // handle the error
    End If
End With

If you want to use the displayed value in code without performing the conversion in VBA then use the Text property of the cell (Range) rather than the Value property. You'll need to be sure that the cell is formatted correctly first:

Dim hourValue As Integer

With Worksheets("Sheet1").Cells(1, 1)
    If ((.NumberFormat = "H") And _
            (IsDate(Format(.Value, "hh:mm:ss")))) Then
        hourValue = CInt(.Text)
    Else
        // handle the error
    End If
End With

If you just want to use the hour value in a worksheet formula then use the HOUR() worksheet function - e.g. =HOUR(A1)

Finally, if you cannot format the column manually because the sheet is protected then you won't be able to format it in VBA either