Excel: Separating Time & Date
By Richard Rost
15 years ago
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).
Comments for Excel: Separating Time & Date
The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved.
Current Time: 12/8/2023 5:31:44 AM.