convert from Us date format to UK

Hi
I have an excel file which hold a huge data.  some of its rows of the date column in Us format and other in UK. How can make them all in UK format?

The format as following  

mm/dd/yyyy  hh:mm:ss      and other like that     dd-mm-yyyy   hh:mm:ss

I need it to be  dd/mm/yyyy   hh:mm:ss
amq10Asked:
Who is Participating?
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

I think your formula is okay, but you need to check the formatting on the cell containing the formula.

Format the cell containing the formula using this format:

dd/mm/yy hh:mm

and it should work - at least it does for me!

Alan.
0
 
amq10Author Commented:
The format as following  

mm/dd/yyyy  hh:mm:ss      and other like that     dd-mm-yyyy   hh:mm:ss

I need it to be  dd/mm/yyyy   hh:mm:ss
0
 
amq10Author Commented:
Now I can change it using the below formula:

=IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))


the result

09/12/2013 15:00      12/09/2013 00:00

however, the time doesn't  change
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
amq10Author Commented:
HI Alan

I have tried now to chabge the format of the formula cell but stell give time like that 00:00:00
!!!!
0
 
amq10Author Commented:
Thanks I found did the solution:

I have calculate the formula on the main cell, then calculate the time of the main one using
=Time(hour(main cell), minute(main cell),second(main cell)) and then add the date with time

A: main cell     B:formula(a)   c: time(A)    D: B+C
0
 
amq10Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for amq10's comment #a39725502
Assisted answer: 500 points for Alan3285's comment #a39725436

for the following reason:

the reason in the second comment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.