I have an excel sheet that was imported via txt file but I do not have a copy of the txt file and our programmer is out this week. I hope someone can assist. Currently it is listed as DD MM YY (with spaces) and I need it to convert to dd-mon-yy.

Any assistance is much appreciated!

Chellie

The solution all depends on how the date is currently formatted. It sounds like it is there as text and I am assuming that 1st February 2009 would be shown as 01 02 2009.

That being the case, you will have to re-assemble the pieces to make a date that Excel recognises and then you can use normal Excel formatting to get it to display however you want.

The way I would do this is to put a formula into an empty column which looked something like this:

=Date(Right(Trim(Nx),4), Mid(Trim(Nx),4,2), Left(Trim(Nx),2))

where Nx is the location of your data (N being the column and x being the row). The Trim function is used to remove any unwanted spaces that are there but you cannot see and might otherwise much this up.

Now you should have a date and you can format the cell (or whole column) using the Format function and choosing the Number tab - there are some defaults available for Date, but if you cannot find the one you want then choose Custom and type in dd-mmm-yy.

Now you can shuffle your columns around and hide the original.

Hope this all helps.

Thanks,
Caspar

Caspar you are my new best friend! It worked perfectly. Thank you so much!