date and time cells

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

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

Date and Time in Cells

resolvedResolved · Urgent Priority · Version 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:

Quickly create a list of numbers

The fill handle in Excel has many uses. If a number is typed into a cell and then the fill handle is used, the number will be copied from one cell to the next.

If you wish to create a list of numbers that increment by one at a time, hold down Ctrl in conjunction with using the fill handle - this should give you a series of numbers (e.g. 1, 2, 3 etc) rather than copying a single number.

View all Excel hints and tips


Server loaded in 0.09 secs.