Date Format incorrect - Excel 2003

[Busymanjohn]

Hi guys, I have tried various things to no avail and get get the date format ( some cells only ) in a range of data. The xls file is an extract from Brioand normally have no issues with re-formatting the dates form US format to UK format, but today some of the cells would not change, they stay as 09/14/12 ,, when they should read 14/09/12. I have tried formatting, copy paste special as values, text to columns, a 0 in an empty cell, copy paste special>Add ,,, nothing works. Is there maybe something else I could try or does this sound ( like it does to me ) like a Brio issue ? ( and if so, why does it only affect some dates and not all ?).

 

[Luke M]

Can you confirm that the data is actually stored as a date and not as text? If XL thinks it's text, then no amount of cell formatting will have any affect, and we'll need to manipulate the data first.

 

[Busymanjohn]

Hi Luke, that's the one thing I am awaiting feedback on from the guy who owns the file. I just wondered if there was anything else we could do in Excel ,,, doesn't sound like it.

 

[sdsurzh]

Hi Busymanjohn,


Try text to columns


select the data and select text to columns, select delimited,select tab and press next,then select Date and change the format from MDY or DMY or Select from the drop down and press finish. Hope this will help you.


Thanks,

Suresh Kumar S

 

[Busymanjohn]

Hi Suresh, had already tried that ,,, doesn't help, must be something with the original Brio file ,, maybe the date is set as text within that source.

 

[SirJB7]

Hi, Busymanjohn!


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!