How to change date format from US to UK

[MAHMUZ]

Hi everyone

I am trying to find a way in Excel 2007 to convert the date from US format to UK format, I tried to change it by using [right click=>Format cell=>Select Date=>Select the first option in Type:*14-03-01=>OK], but I doesn’t work , the attached file are presenting the date in US format, please I need your help and support.

BR

[Marcol]

I'm not sure what is going on with your sheet, I suspect it has beem formated as text at some stage.

However this seems to solve your problem
In D6

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

Drag/Fill Down

Hope this helps

[MAHMUZ]

Many thanks for your support & help, your input was very helpful but in long run, I am trying to get easier solution, but never mind its good and better than nothing.

[DonkeyOte]

In terms of alternatives:

Highlight your Column -> Data -> Text to Columns -> Step 3 choose Date -> MDY -> click Finish.

[mimidparis]

Dear Donkey,
Your suggestion is simply BRILLIANT.
Thanks.

[N_DONOVAN]

Marcol

I found this post it works to change the date, but when I try and copy it to the cell I want it, even with paste special, it doesn't seem to work.

Thanks,

nick

[skate1991]

Hi,

I have tried using the formula by marcol and it works but I need I have time in the date aswell and its changing the time to midnight does anyone know a way to keep the relevant time.

Thanks

[Marcol]

@ skate1991
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

After 130 posts, I'm a little surprised that you are not aware of this ...

[skate1991]

Thanks for the reminder Marcol, I will make sure going forward I do that,

Any Ideas about the answer ?????

[Cutter]

@ skate1991

As Marcol pointed out, your post contravenes forum rules and yet you ask again right after acknowledging his post.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[jeffreybrown]

Hello Nick & Welcome to the Forum,

Please read post #6 and #8 as it applies to all.