Q: How do I remove the date from a cell with both the date and time (the date seems to show up automatically, although I'm only interested in the time). In short, I want to go from "4/7/2008 6:18:00 AM" to "6:18:00 AM". Thanks, Lauren.


A: Lauren, the only easy way I've found to do this is to break up the date/time into its components (day, month, year, etc.) and then put them back together again with the DATE() and TIME() functions.

For example, let's say A1 has: 4/7/2008 6:18:00 AM

Put the following functions in the next cells:

B1 = YEAR(A1)
C1 = MONTH(A1)
D1 = DAY(A1)
E1 = HOUR(A1)
F1 = MINUTE(A1)
G1 = SECOND(A1)

Now put them all back together:

H1 = DATE(B1, C1, D1)
I1 = TIME(E1, F1, G1)

That's about the only easy way to do it and still keep the value as a DATE/TIME and not text (you could do it with string concatenation too, but you would then have to convert it back to a date).

Subscribe to Excel: Separating Time & Date
Get notifications when this page is updated