Change number to date

Originally Posted by sioncleverly

Hi,

I have a problem, i am currently importing data to my excel spreadsheet through ODBC, this is not a problem and it works fine however the date i am getting is shown as 90307 which everyone knows is 09/03/2007 or 9th March 2007. (UK Date)

I have tried changing this to a date format but i am getting this 2147-04-01! I can't find a way for making 90307 to be 09/03/07!

Can anyone help? i need a simple way of doing this as i will be recording a macro to do it.

Thanks

Sion

Hi
if your data is in A1 put in B1
=DATE(RIGHT(A1,2),IF(LEN(A1)=5,MID(A1,2,2),MID(A1,3,2)),IF(LEN(A1)=5,MID(A1,1,1),MID(A1,1,2)))
and format cell as Date (it will only show year as 07 not 2007)

hope this would serve your purpose.

Hi

Same result different flavor

Please Login or Register  to view this content.