Excel: Convert US date format to European format

Question

I have the following US date format 5/30/2018 8:00:00 AM. I am looking for an optimized formula to convert it into European format 30/05/2018 (dd/mm/yyyy) without the time. I have searched the net for a good solution, was not able to find an easy one. I am looking for a formula and not to format cells etc

Moreover my computer is set for European format, which reads months and days wrongly. Any changes in excel to overcome this situation would be helpful.

This question is not for VBA but i am requesting a formula to be entered in a cell. Also the source cell is not formatted as a number/date... its just a general cell or custom cell

If you dont want to format cells means, Then whatelse you need ? — Deepak, Feb 07 '18 at 07:58
First it is *'I am looking for an optimized formula ...'* then it is *'I am not looking for a formula...'*. Which is it? — , Feb 07 '18 at 08:09
sorry, my mistake while typing. I have edited the question. Thank you — Prashant Balasubramanyan, Feb 07 '18 at 08:13
@Jeeped This question has been edited to specifically mention not using VBA. Is this still a duplicate? — Rob, Mar 28 '18 at 00:47
@Rob - Yes. Worksheet formula and other worksheet operation solutions are discussed in several of the answers. — , Mar 28 '18 at 02:06

Prashant Balasubramanyan · Accepted Answer · 2018-02-07T09:50:42.410

4

In the meantime, I came up with this formula

=IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,(FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1))))

Any other optimized formulas are welcome

edited Feb 07 '18 at 09:50

answered Feb 07 '18 at 08:38

Prashant Balasubramanyan

129
3
11

this is working great i am using this formula in my files – Filip Ondo May 17 '18 at 08:30
happy to hear its working and can be used as a generic formula – Prashant Balasubramanyan Sep 11 '18 at 13:12

score 1 · Answer 2 · answered Feb 07 '18 at 08:18

1

this will fix it

=TEXT("9/1/2017 12:00:00 AM","dd/mm/yyyy")

you can change accordingly

answered Feb 07 '18 at 08:18

Praveen DA

358
4
17

sorry have tried this. This works only if the cell is a number/date, it does not work for me I get a #value when i use this formula – Prashant Balasubramanyan Feb 07 '18 at 08:35
@PrashantBalasubramanyan You should have probably explained this in your question then. Anyways, I am sure this will help someone else in the long run (unlike OP & his vague question), so +1 – K.Dᴀᴠɪs Feb 07 '18 at 08:40

Excel: Convert US date format to European format

2 Answers2

Linked