Column of dates not recognized as dates...

[nanomess]

Hi,
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?

[Ryan_G]

Select cells that have dates (or click the column header letter to select the entire column), go to Format Cells>Number Tab>Date and select the format you want.

[judgeh59]

just a bit confused.....why would "Jul/13" convert to "01/06/2013"....July vs January?.....also just formatting does actually change the data only how it's shown. If you intend on doing date math you will have to actually convert it to a real date and there is a couple of formulas to do that....one of the them is:

=TEXT(A1,"MM/DD/YYYY") assumes you data is in A1.......HTH

[FDibbins]

Thats probably because they are NOT dates, but text that just looks like dates.

test with =isnumber(cell_ref)......false is TEXT, but if your dates are in fact like your sample, they almost certainly are not dates. in an adjacent column, enter =A1*1 and copy down (assuming your data is in A1)

@Ryan, changing the format of a cell doesnt not change/affect the contents of that cell, only its appearance or "cosmetics", so if the cell contains text, formatting wont fix that

Edit: @ judge...english data format...day/month/year

[judgeh59]

@FDibbins - tie goes to the runner?

[Ryan_G]

@Ryan, changing the format of a cell doesnt not change/affect the contents of that cell, only its appearance or "cosmetics", so if the cell contains text, formatting wont fix that

OP didn't say anything about doing math, I assumed they were only concerned about how it displayed. Oh well. I learned something too!

[judgeh59]

@FDibbins -- thanks on the English Format.....I looked at that and completely forgot

[nanomess]

@FDibbins

Thanks,
That was exactly the case... isnumber returned false... and doing cell*1 converted it into a formattable date.

[nanomess]

Yup! English format. Likewise I get confused by the US format sometimes. :P