Dates in incorrect format

Resolved · High Priority · Version 2007

Wed 28 Jan 2009, 16:01 Delegate Tom said...

Tom has attended:
Excel Advanced course

Dates in incorrect format

I have a list of dates that are in the incorrect (i.e. American) format e.g. 12/16/2008, that I need to convert into the British version e.g. 16/12/2008. Normally I would change to UK in the date format tab, however these entries have been formatted as text not date so doing this has no effect.

Furthermore, I need to have the corresponding year in a seperate column but for those dates that are the wrong way round, the YEAR function does not work!

Any ideas?

Cheers
Tom

Fri 30 Jan 2009, 13:20 Trainer Amanda said...

RE: Dates in incorrect format

Hi Tom

Thank you for your question.

Would it be possible for you to send a small sample of your data so we can have a play around with it here and see if we can get something to work? If so please email to amanda@stl-training.co.uk

If it's not possible to email anything, please reply in kind to this post.

Kind regards
Amanda

Mon 23 Feb 2009, 09:51 Trainer Amanda said...

RE: Dates in incorrect format

Hello Tom

Thanks for sending through a sample of dates.

I've been able to extract the day, month and year from the original date using the MID function; then combine the date, month and year together again in UK format using the DATE function.

I've attached an example so you can see how the MID and DATE functions are used.

To transpose the dates from columns to rows, copy the dates then select the cell that will represent the starting point for the dates once they are pasted in. Go to the Home ribbon, click the dropdown arrow under Paste Special, and select Paste Special.

Select Values and Number Formats, and tick the Transpose box. Click OK.

I hope this helps.
Amanda