Convert GENERAL cells to DATE, please help

[sergiopcrt]

Hi everyone,

I have a tough issue I need to solve quickly and I really need your help here.
I have a DATE column where in some of the records the date appears in Excel formated as GENERAL (aligned to the left in the cell) and I I tried everything to changed it to date but to no avail.

The correct records are formated as 15/03/2014 and the incorrect ones are 23-Ago-2014.

[TABLE="width: 107"]
<tbody>[TR]
[TD="align: right"]29/11/1953[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2000[/TD]
[/TR]
[TR]
[TD]18-Dec-1975[/TD]
[/TR]
[TR]
[TD]05-Dec-1977[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2000[/TD]
[/TR]
[TR]
[TD="align: right"]16/12/1971[/TD]
[/TR]
[TR]
[TD="align: right"]09/04/1960[/TD]
[/TR]
</tbody>[/TABLE]

I've tried the format cells menu, the DATEVALUE, DATE and TEXT functions and nothing changed those cells. I've also tried the TEXT-TO-COLUMNS trick, but no luck.
From my research so far nothing worked, and I wonder if there's any way around this even if through VBA code.

I'm using Excel 2010 and I intend to later export this info to Access and if the records are not properly formated as date, Access will return an error and won't import those different formated records.

Thanks in advance,
Take care
Sérgio

 

[mole999]

if you can't go back to the original import and make sure its delivered in the correct format,

taking the top value 29/11/1953 if that were in A1 you could check by using in B1 as an example =ISNUMBER(A1),

most date issues will occur in numbers less than 12

 

[Rick Rothstein]

I've also tried the TEXT-TO-COLUMNS trick, but no luck.

I am curious... exactly what happened when you tried the Text To Columns trick?

 

[sergiopcrt]

Mole999: ISNUMBER function just returns false and it seems those cells are behaving like TEXT

Rick Rothstein: using text-to-Columns on the GENERAL cells (and choosing data type as Date) does absolutely nothing to those cells.

 

[MARK858]

What are your regional date settings? Asking because in UK English i.e. dd/mm/yyyy all the dates are coming out as real dates for me when copied into Excel.

(and choosing data type as Date)

Don't choose Date, leave it as the default General and click Finish.

 

[sergiopcrt]

What are your regional date settings? Asking because in UK English i.e. dd/mm/yyyy all the dates are coming out as real dates for me when copied into Excel.



Don't choose Date, leave it as the default General and click Finish.

MARK858: This is for work and I don't have admin rights to access my regional settings, although I don't think that's the problem, as only some cells are affected. The problem came from the original source's data entry, which I most likely won't be able to control.
As for the Text-to-columns, I tried every option and none makes a difference on the cells formatted as GENERAL.

Maybe there's a way to do this with VBA code, going through the date column and force the GENERAL cells to convert to Date?

 

[MARK858]

Formatting the cells to date only alters the appearence of a number (which is what a date is), it doesn't change the value.
You need either VBA or a formula to manipulate the string to a format your computer recognises.
I am not at home to post anything until at least tonight.

 

[sergiopcrt]

Formatting the cells to date only alters the appearence of a number (which is what a date is), it doesn't change the value.
You need either VBA or a formula to manipulate the string to a format your computer recognises.
I am not at home to post anything until at least tonight.

Thanks MARK858, any help will be greatly appreciated

 

[sergiopcrt]

Does anyone have any idea? I really need your help here.
Maybe this is possible via VBA code?