Forum Discussion
How to remove time from date and time data in one cell
I have an Excel spreadsheet of 2000 records with the date DD MM YYYY and time HH:MM:SS in the same column and I want to remove the time detail from the column. Any suggestions how I might do this ?
Tell us a bit more, such as describing how the two categories of information are displayed. Is it one field of date and time, or two separate fields crammed into the same cell? Ideally, give us a sample file.- Sorry - I should have clarified that the date and time are both in the same cell presented as DD MM YYYY HH:MM:SS as in 28 05 2024 16:12:14. I'm trying to do a find & replace to remove all the time data and leaving the date data. Does that help?
If this display is the result of a formula, then you can modify the formula to =INT(prior formula) and just format as short date. Copy the formula and format to the entire column
The reason that would work is that the time is a decimal representation of a fraction of a day, whereas the date is an integer. So reducing the whole value to an integer will give you the date without the time.
If, on the other hand, it's a simple value (integer plus decimal), then I'd use a "helper column" to create the date alone.....and you could then use copy...paste special...values and format to over-write the original, and then get rid of the helper column.
- To remove the time from a date and time data in one cell, you can use the following formula in Excel:
scss
Copy code
=INT(A1)
This formula extracts just the date part from the date and time value in cell A1 by converting the value to an integer, effectively removing the time.
