Excel - remove time stamp from date in excel

Asked By Nmeye on 07-Aug-07 11:26 AM
I have pasted data from project into excel. I have the project dates with a
time stamp 07/14/07 10:12 PM. I want to strip the time portion off from the
date. Format just changes the appearance but does not remove the hidden time
stamp.




RonCoderr replied on 07-Aug-07 11:36 AM
If you want to actually truncate the dates before the time stamp....

Try this:

Select the column of date/times to be impacted

From the Excel main menu:
Step_1: Check: Delimited..........Click [Next]
Step_2: Check: Space..............Click [Next]
Step_3: Check: Select the 2nd column (with the time in it)
..........Check: Do not import column
..........Repeat for any other columns to the right of the date
..........Click [Finish]

That will replace the Date/Time values with just the Date portion
(Remember to set the number format to one that only displays a Date)

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP
Nmeye replied on 07-Aug-07 11:50 AM
Thanks Ron. This seemed to work.
Tony Mitchell replied on 15-Aug-07 12:20 PM
Ron - can you explain how to start with the steps you recommend. I don't see where to select delimited from the Excel main menu as noted. The data I need to truncate is already pasted into a column so I don't need to import the data.



Thanks,



Tony
RonCoderr replied on 15-Aug-07 12:36 PM
Copied from the previous post and annotated:

First: Select the column of date/times to be impacted
(so if your 'dates' are in A2:A500....select that range)

Then...from the Excel main menu:
Click: <data><text-to-columns>
Step_1: Check: Delimited..........Click [Next]
Step_2: Check: Space..............Click [Next]
Step_3: Check: Select the 2nd column (with the time in it)
..........Check: Do not import column
..........Repeat for any other columns to the right of the date
..........Click [Finish]

Note: I enclose menu commands with these characters <>.
Example: <file><save>means:
.....Click FILE from the Main Menu
.....Click SAVE from the list

Does that help?
***********
Regards,
Ron

XL2003, WinXP
Tris replied on 14-Nov-07 05:38 AM
Hi I have tried this using a date only column but when I mail merge to word it
always puts in the time element which I do not want.  How can i stop this
happening.

Cheers
Trish
RonCoderr replied on 14-Nov-07 09:43 AM
Try this in your Word document:

Right-click on the date field
Categories: Date and time
...set an appropriate date format that does not include a time component.
Click [OK]

Does that help?
***********
Regards,
Ron

XL2003, WinXP
RonCoderr replied on 14-Nov-07 09:46 AM
Drat! I missed a step...

the instructions should be:

Right-click on the date field
Check: EDIT FIELD
Categories: Date and time
...set an appropriate date format that does not include a time component.
Click [OK]

***********
Regards,
Ron

XL2003, WinXP