American Date to English Date Format.

Hi,

I have a report that runs from a browser that pulls the date in American Format. MM/DD/YY

Is there a forumula that will convert it as.

Here is example :

Next Review Date
10/31/2016 12:00:00 AM
10/31/2016 12:00:00 AM
10/28/2016 12:00:00 AM
10/31/2016 12:00:00 AM
11/03/2016 00:00
10/31/2016 12:00:00 AM
10/31/2016 12:00:00 AM
11/02/2016 00:00
10/31/2016 12:00:00 AM
11/02/2016 00:00
11/07/2016 00:00
10/31/2016 12:00:00 AM
11/02/2016 00:00
11/03/2016 00:00
11/07/2016 00:00
11/01/2016 00:00

Im not fussed about the date although that will always be there.
I was hoping I could insert a column to the right of the dates and put a formula in so it switches the date from 10/31/2016 12:00:00 AM to 31/10/2016 12:00:00 AM.

Thanks in advance.

Nath'

Thanks Richard. Ive used the formula and it works on some.

So the dates that are like the below reverts the dates

11/23/2016 12:00:00 AM 23/11/2016
11/17/2016 12:00:00 AM 17/11/2016
10/31/2016 12:00:00 AM 31/10/2016
10/31/2016 12:00:00 AM 31/10/2016

However any of the dates where the time shows like the below gives me the #VALUE!

11/03/2016 00:00 #VALUE!
11/02/2016 00:00 #VALUE!
11/02/2016 00:00 #VALUE!
11/07/2016 00:00 #VALUE!
11/02/2016 00:00 #VALUE!
11/03/2016 00:00 #VALUE!

Any suggestions?

Ive uploaded the file for you.

I think this the location of the file.

http://www.excelforum.com/attachment...1&d=1477646470

Thanks

Nath'

Going off the text string ISNUMBER some are showing as False but some a True which may be the reason why it will only pull some of them.

What would you suggest?

Thanks

Nath'

Put this formula in B2:

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)))

then copy down.

Hope this helps.

Pete