Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

date and time cells

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Date and Time in Cells

Date and Time in Cells

ResolvedVersion 2007

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

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

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

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

Excel tip:

Screen Splitters in Excel

Screen splitter icons can be set from the ribbon bar, or dragged from the scroll bars. The icon just above the up arrow on the right scroll bar controls the horizontal splitter; the icon to the right of the right arrow on the bottom scroll bar controls the vertical splitter.

These icons can be double-clicked to split the screen at the top left of the cell currently in use.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.