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

Excellent! That works perfect.

Thanks Richard & Pete for the help!

Actually, sorry, Im coming across another issue it.....

Other rows have other times in it?

Ive attached the sheet again. The ones highlighted in blue were part of the first which is resolved.

I have no idea why this report spits out date formats in the US format and with and without times.

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

You can use this formula instead in B2:

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2,FIND("/",A2)+4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))

then copy down.

Hope this helps.

Pete

Thanks so much Pete! Works Perfect! :D

Well, that's good to hear - thanks for the reps.

Let me know if you also want to extract the times.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete