How to convert 20030707 to 07/07/2003

[morlan]

Hi,

Someone sent me a spreadsheet with about 4000 entries.

Anoyingly they have enter the date of each record as yyyymmdd format without spaces. In order to import this information to the database I need to convert yyyymmdd to dd/mm/yyyy.

Does anyone have any ideas how I could convert this to the proper format? I have tried format cells as date but this converts the dates to #########

Any suggestions appreciated.

 

[fmm]

The following formula will convert a text field in the form YYYYMMDD to a date field:

CDate(Left(Right([TextDate],4),2) & "/" & Right([TextDate],2) & "/" & Left([TextDate],4))


[TextDate] is the field the data is stored in.

I would create a temp field and update it with the formula above.

Then change the type on the original field and update with the datat from the temp field.

Good luck.

 

[morlan]

The following formula will convert a text field in the form YYYYMMDD to a date field:

CDate(Left(Right([TextDate],4),2) & "/" & Right([TextDate],2) & "/" & Left([TextDate],4))


[TextDate] is the field the data is stored in.

I would create a temp field and update it with the formula above.

Then change the type on the original field and update with the datat from the temp field.

Good luck.

Thanks friend, that really helps