5/19/2008 1:11:52 PM
Excel: Separating Time & Date
By Richard Rost
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).
Keywords: excel tips year month day hour minute second date time