How to extract Hours from the date time format 12/10/2012 12:18:58 PM?

Question

How do I extract the hours from the date time format 12/10/2012 12:18:58 PM?

I found this Hour(Format(d1, "mm/dd/yyyy HH:mm:ss HH24")) on another Stack Overflow question, but not working for me.

Solution 1

This should work

=TEXT(D1, "hh")

and format as time

Solution 2

If D1 contains a Date/Time as Date Time Serial Number or as a string recognisable as a date time then use =Hour(D1)

Eg D1 = the string 21/03/2013 18:45:00 =Hour(D1) = 18

Eg D1 = 41354.781250 then =Hour(D1) = 18
Note: D1 may contain a value but be formatted to display as a date/time. Check the cell number format.

To do this in VBA try this

Sub demo()
    Dim rng As Range
    Dim Hr As Long

    Set rng = Range("D1")

    Hr = Hour(rng.Value)

    Debug.Print Hr
End Sub

View the result in the Immediate Window

Solution 3

Try with:

Hour(format(now(), "yyyy-MM-dd hh:mm:ss"))

Solution 4

if "12/10/2012 12:18:58 PM" is in cell A1:

=TIME(HOUR(a1),MINUTE(a1),SECOND(a1))

After the formula is entered, you can right click on "format cells" and select the time format you want to see. I've verified this formula in Excel 2013.

View more solutions