mike637
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
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
Format the cell as time, (rt click and select format cells, number tab then time)?
Chris
Chris
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?
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
Chris
Sub change1()
Set MyRange = Range("a1")
With MyRange
.Value = CStr(Format(.Value, "h:mm:ss AM/PM"))
End With
End Sub
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you gentlemen for your assistance.
It worked either way.
meck637
It worked either way.
meck637
Open in new window