VBA to extract time only from Datetime format

[aryanaveen]

Hi All,

Please help me with below


I need a code which will do below.

Cell G1 has date and time - 11/07/2016 4:45:29 PM
now I need a code which will remove date and only time should be available

cell G1 should return value as 4:45:29 PM

 

[Scott Huish]

Sub ChangeDateToTime()
With Range("G1")
    .Value = TimeValue(.Value)
    .NumberFormat = "h:mm:ss AM/PM"
End With
End Sub

 

[Joe4]

Are you going to be using the value in G1 in any calculations?
If not, simply format it to just show the time piece.

By the way, it is important to understand how Excel stores dates and times. They are really stored in Excel as numbers.
The date part is the number of days since 1/1/1900, and the time piece is just a fraction of one day (if you change the format to General, you will see how Excel sees/stores the value).
So, to get just the time portion of a date/time, simply subtract the date portion (which would be the integer portion of the date), i.e.
=G1 - INT(G1)
This will return just the time piece (be sure to choose the appropriate time format, if you want to see it in a time format and not General).

 

[Sektor]

=TIME(HOUR(G1),MINUTE(G1),SECOND(G1))
Format cell as you need or use "TEXT" function.

 

[aryanaveen]

Hi Scott

Below line shows an error
.Value = TimeValue(.Value)

Type mismatch
Cell G1 is in time format but still I get this error

 

[Scott Huish]

I can only make it fail when it only contains the time (or any other non date/time text), when it contains the full date and time as in your sample, it works fine.

This will keep it from failing, if you don't have the necessary data in the cell.

Sub ChangeDateToTime()
On Error Resume Next
With Range("G1")
    .Value = TimeValue(.Value)
    .NumberFormat = "h:mm:ss AM/PM"
End With
End Sub

The op asked for the value to be changed in place which is why I didn't offer a formula, but if you want that, then this will work too:
=MOD(G1,1)
Format as:
h:mm AM/PM

 

[Dave Patton]

a
ignore; incorrect thread