There are often instances in which you have date and time information in a single column in an Excel spreadsheet and it is necessary to remove one or the other.
Removing Time from Data with Date and Time
1. Go to the cell where you wish have the date without the time (cannot be the current cell with the date). You may need to insert a column.
2. Type the following: =date(year(cellname),month(cellname),day(cellname))
a. Example: In cell B2 type the following if the date and time were in cell A2: =date(year(a2),month(a2),day(a2))
3. Push "Enter."
4. Copy the formula to all other necessary cells.
Removing Date from Data with Date and Time
Standard Time
1. Go to the cell where you wish have the time without the date (cannot be the current cell with the date). You may need to insert a column.
2. Type the following: =time(hour(cellname),minute(cellname),second(cellname))
a. Example: In cell B2 type the following if the date and time were in cell A2: =time(hour(a2),minute(a2),second(a2))
NOTE: Even if seconds are not originally appearing in the time, they must be included in the formula for it to work.
3. Push "Enter."
4. Copy the formula to all other necessary cells. Data will look like the example:
Military Time
1. Go to the cell where you wish have the time without the date (cannot be the current cell with the date). You may need to insert a column.
2. Type the following: =time(hour(cellname),minute(cellname),second(cellname))
a. Example: In cell B2 type the following if the date and time were in cell A2: =time(hour(a2),minute(a2),second(a2))
NOTE: Even if seconds are not originally appearing in the time, they must be included in the formula for it to work.
3. Push "Enter."
4. Right click on the cell where the formula was entered and select "Format Cells."
5. In the "Format Cells" dialog box, on the "Number" tab, select the category "Custom," and choose the type "h:mm" or "h:mm:ss" depending on whether seconds need to appear.
6. Select "Ok."
7. Copy the formula to all other necessary cells.
These instructions, along with illustrations, can also be found in SharePoint > Software Users Group > Shared Documents > Excel > Removing Time from Data with Date and Time and SharePoint > Software Users Group > Shared Documents > Excel > Removing Date from Data with Date and Time