Forum home » Delegate support and help forum » Microsoft Excel Training and help » Date and Time in Cells
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Date and Time in Cells
Resolved · Urgent Priority · Version 2007
Reply Mon 19 Oct 2009, 16:48Delegate Rees said...
Rees has attended:
Excel Intermediate course
Excel Advanced course
Excel VBA Intro Intermediate course
Date and Time in Cells
Hi Guys
I am currently using data produced via a TSV file in an Excel spreadsheet. The problem I have is that the time and date reference appear in the same cell. When I change the format of the cells to "date" or any other date format in the Custom Format tab, it shows just the date, however, when I click on the cell the time still comes up.
I am analysing the data by date only and therefore need to find a quick and easy way to get rid of the specific time stamp in the date cells.
Any help is most appreciated.
Many thanks
Rees
For upcoming training course dates see: Pricing & availability
RE: Date and Time in Cells
Hi Rees
Thank you for your question.
How does the date and time appear exactly in the cell when the data is imported?
Kind regards
Amanda
Reply Mon 19 Oct 2009, 17:23Delegate Rees said...
RE: Date and Time in Cells
Hi Amanda,
Date appears like this:
10/09/2009 07:50:29
As i mentioned previously. Would like to get rid of the time stamp, but as the report is produced from TSV code, the time won't disappear when I change the format.
Thanks
RE: Date and Time in Cells
Hi Rees
Thanks for this. I wanted to know if there was a unique character that separated the date from the time, which in this case there does appear to be (i.e. a space).
Assuming the dates are listed in a column, hopefully this means you should be able to use the Text to Columns feature to split the date away from the time stamp; then just delete the time information as it's not required.
To use Text to Columns:
1. Select the column containing the dates.
2. Go to the Data tab, and click the Text to Columns button.
3. The Text to Columns Wizard will start. At step 1, select Delimited and click Next.
4. At step 2, tick just the Space box. You should see in the preview at the bottom of the wizard that the date and time show in two different columns. Click Next.
5. At step 3, select the column containing the times, and select Do not import column (Skip). Then click Finish.
If the dates remaining still show 00:00 next to them, select the column and reformat it to dd/mm/yyyy. This should get rid of the 00:00
I hope this helps.
Kind regards
Amanda
Reply Tue 20 Oct 2009, 09:36Delegate Rees said...
RE: Date and Time in Cells
Hi Amanda,
That works perfectly. Thank you so much. It's been giving me such a headache.
Kind regards
Rees
|