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
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
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
US to UK dates.xls
