vba truncate date to show time only

mike637
mike637 used Ask the Experts™
on
Hello experts:

I am running an import to bring in time from another workbook.  However, I am getting the date pulled in as well as "1/1/1900" depending on which worksheet I import from.  I can not seem to find the code to truncate the data in the cell to drop this date?

i.e - the cell value is showing "1/1/1900 3:00:00 AM".  I need "3:00:00 AM" as the value.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
All true Excel date/time values contain both a date and a time.  To display only the time, do something like this:

Set MyRange = Range("a1")
With MyRange
    .Value = Time
    .NumberFormat = "h:nn:ss AM/PM"
End With

Open in new window

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Format the cell as time, (rt click and select format cells, number tab then time)?

Chris

Author

Commented:
Hi mattewspatrick,

This changes the formatting in the cell, however in the formual bar - the affected cells still show 01/01/1900 followed by the imported time.

Again, this is isolated to certain cells in my range("Z13:AM22").  Is there a way to take this range and truncate the date so it only appears as time?
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
With a small change to Patricks code the value could be string of the form.

Chris
Sub change1()
    
    Set MyRange = Range("a1")
    With MyRange
        .Value = CStr(Format(.Value, "h:mm:ss AM/PM"))
    End With

End Sub

Open in new window

Top Expert 2010

Commented:
mike637,

Like I said, there is no such animal as a time without a date.  The key is how you want to display it, and I have shown you exactly how to do that.

Patrick
Software Quality Lead Engineer
Top Expert 2011
Commented:
Adapting the method to work on a range of cells then for example:

Chris
Sub change1()
Dim rng As Range
Dim cel As Range

    Set rng = Range("Z13:AM22")
    For Each cel In rng.Cells
        With cel
            If cel <> "" Then
                .Value = CStr(Format(.Value, "h:mm:ss AM/PM"))
            End If
        End With
    Next

End Sub

Open in new window

Top Expert 2010
Commented:
You could also try this:

Set MyRange = Range("Z13:AM22")
With MyRange
    .NumberFormat = "h:nn:ss AM/PM"
    For Each cel In .Cells
        cel = cel - Int(cel)
    Next
End With

Open in new window

Author

Commented:
Thank you gentlemen for your assistance.

It worked either way.

meck637

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial