Date Format Conversion

[BanburyS]

I have trawled the net, and tried countless bits of VBA code, but nothing seems to do what I need. I have a column of dates from BL2 to BL10000 with date in all sorts of formats. I need them all to end up as the format 01.03.17. Any ideas would be very much appreciated.

 

[Jonmo1]

It's going to be extremely difficult to come up with a single solution for dates in 'all sorts of formats'

Can you perhaps post several examples showing every possible variation of format ?

 

[BanburyS]

Wow thanks for your quick reply. Looking at the data,. It seems they can be say DD.MM.YY which is what we need, or DD/MM/YY or D/M/YYYY or the same combination but with - as the separater. None of them seem to hg have any text for the month or days. Any ideas?

 

[Jonmo1]

Try highlighting the column, Go to Data - Text To Columns
Select Deliminated - next
Choose Date - DMY
Click Finish.

 

[Amanda Wilso]

1. In an Excel sheet, select the cells you want to format.
   Press Ctrl+1 to open the Format Cells dialog.
   On the Number tab, select Custom from the Category list and type the date format you want in the Type box.
   dd/mm/yy and =SUBSTITUTE(A2,"/".")

 

[BanburyS]

Thanks for advice and help, I will give that a go