Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

asked on

vba truncate date to show time only

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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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

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

Chris
Avatar of mike637

ASKER

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?
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

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
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike637

ASKER

Thank you gentlemen for your assistance.

It worked either way.

meck637