remove time stamp from date in excel

G

Guest

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.
 
G

Guest

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:
<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]

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
 
G

Guest

Thanks Ron. This seemed to work.

Ron Coderre said:
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:
<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]

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


Nmeyer said:
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.
Click to expand...
Click to expand...
 
T

Tony Mitchell

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

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
G

Guest

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
 
G

Guest

Hi I've tried this using a date only column but when I mail merge to word it
always puts in the time element which I don't want. How can i stop this
happening.

Cheers
Trish

Ron Coderre said:
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:
<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]

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


Nmeyer said:
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.
Click to expand...
Click to expand...
 
G

Guest

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


Trish said:
Hi I've tried this using a date only column but when I mail merge to word it
always puts in the time element which I don't want. How can i stop this
happening.

Cheers
Trish

Ron Coderre said:
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:
<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]

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


Nmeyer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Ron Coderre said:
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


Trish said:
Hi I've tried this using a date only column but when I mail merge to word it
always puts in the time element which I don't want. How can i stop this
happening.

Cheers
Trish

Ron Coderre said:
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:
<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]

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

separating date and time 5
vlookup between two time stamps 1
A Query 6
Data list 2
making NT user/login user appear in a cell 1
Date & Time Stamp for printing 1
Date Time Stamp formatting 5
Excel Convert Datetime Stamp to HH and MM Worked 7

Top