Excel: Change cell to DATE data type

[norty303]

Hi, I had a similar problem with a numbers being text data type and someone here posted that you multiply the cell by 1 and it changes to a number data type.

I've got half a column that is a DATE (and responds to formatting for dates and defaults to a right alignment in a cell) and the other half is clearly text as it's left aligned and doesn't respond to date formatting.

I need to link to this excel file from a database analysis tool and create filters on the date but obviously this doesn't work as the data type is not a date.

What's the trick here then?

 

[lupins46]

It probably depends on exactly what you have in your text cells; you can use the pastespecial technique and subtract 0 from each cell - then reformat the result as a date.

 

[AnotherHiggins]

Sounds like the same thing should work. Type 1 into a cell in an empty column (to ensure, or at least increase the odds, that the cell isn't already formatted as text). Highlight that cell, copy it. Select the entire dataset that *should* be dates - even those that already are - and paste special, select Multiply.

If I understand your post correctly, your concern is that since some of the cells are already formatted as numbers, you don't want to mess them up. You won't. Multiplying a number by 1 gives you the same number. Multiplying a number formatted as text forces the cell to number format.

lupins46's suggestion will also work, as will adding zero. Using Paste Special to perform any mathematical function that doesn't change the value of a number will force the text cells to numerical format.

My name is John, and I approved this post.

To get the best answers fast, please read faq181-2886

 

[PeterMoran]

Hi norty303,

In addition to what has been suggested you might identify exactly why the date is not treated as such in Excel.

If you have leading blanks in the field you will need to remove them with using Edit Replace. Multiplying by 1 will not fix the problem. Trailing blanks after a date seem to be ignored in my brief test.

I recently had a situation where unidentified dates in Excel contained "." delimiters. Once these were replace with "/" the cells were identified as dates.

Good Luck!
Peter Moran

 

[torturedmind]

...or you could try using the DATEVALUE() function.

hope this helps. peace!

kilroy
philippines

"Illegitimis non carborundum!"

 

[norty303]

Thanks very much, the 'Paste Special' multiply option was the most attractive but i'm sure they all work very well. I can now get on with my proper work

Cheers all who gave feedback