How do I convert UK date format to US date format in excel?

Chances are, Excel is seeing those dates as “plain text” instead of as date objects. You can confirm this by widening the column with the dates… are they left-justified? If yes, then Excel is treating them as dumb text.

Excel’s builtin Help facility can provide the answer. Or you can see the same instructions online: Convert dates stored as text to dates.

Are the dates stored in date format, or plaintext format? If they’re stored in date format, just apply the correct format. Since that’s simple, I’ll assume that’s not the issue.

If you have dates stored in dd/mm/yyyy format, you can convert it via the concatenate and mid functions:

=CONCATENATE(MID(A1,4,2), ”/”,MID(A1,1,2),”/”,MID(A1,7,4))

(assuming A1 holds the contents of the European-formatted date)

Just click on the cell, right click, go to format cell, and the. It says numerical or date or something like that, and you choose the date set up you want. I don’t have excel on the computer I am currently working on to double check exactly what it is called, but you want to get to format cell.

Also, you can put an apostrophe before the date and it will write it exactly as you type it, rather than converting to how it is formatted currently. The apostrophe will not show once you click out of that cell, but if you need to do it for many cells, better to reformat them, you can do it all at once by selecting the row, if it is all lined up in one row or column.

@JLeslie, if Excel is looking at the value as plain text, it won’t be able to intelligently apply whatever date format you choose.

Try it out yourself by typing in dates like 31/12/2010, 24/05/2009, etc. – note the Euro-layout DD/MM/YYYY – each into their own cell. Select a date format for those cells and…. nothing.

See my first post above.

@robmandu but can’t it still be changed to date format? And, if it is set up as a date, it will be good for the OP to know how to change it. If that is the case I think my explanation is less confusing.

@JLeslie, why don’t you try it out as I explained? Because I did test the solution I posted.

As I said above, the computer I am using right now does not have excel, I can try it later today. I am not saying your answer is wrong, I am saying if it is a formatting issue I think my answer is more understandable. If it is not formatting, then my answer does not apply I guess, unless the OP wants to rewrite the dates.

@JLeslie No. Plaintext cannot be converted to a date.

@JLeslie it’s not a formatting issue, because Excel does not recognize dd/mm/yyyy as a valid date format (at least not by default – it can be added as a custom format). If you simply type “30/10/2010”, it doesn’t matter if you put an apostrophe in front of it or not – Excel thinks it’s plaintext.

@MrItty I see. Ok thanks. I would have assumed the option of switching day and month is a choice.

It gets still more complicated.

If the dates aren’t automatically dd/mm/yyyy, but could be d/mm/yyyy or d/m/yyyy or dd/m/yyyy, then @MrItty‘s formula, as nice as it is for dd/mm/yyyy values, will fail.

This works, but it looks like hell:
=DATE( RIGHT( A1, 4), MID( $A1, LEN( LEFT( $A1, FIND( ”/”, $A1) – 1)) + 2, FIND( ”/”, MID( $A1, LEN( LEFT( $A1, FIND( ”/”, $A1) – 1)) + 2, 4)) – 1), LEFT( $A1, FIND( ”/”, $A1) – 1))

(If your date format includes ”-” or some other character instead of ”/”, then substitute that character in the formula… if you try to use it.)

This is very easy to solve with the Text function.

=Text(A1,“mm/dd/yyyy”)

If that doesn’t work, Excel may be seeing the contents of A1 as text instead of a date. In which case you simply multiply by 1
=Text(1 * A1,“mm/dd/yyyy”)

Also, the simple way to convert the actual text contents to a date is multiply by one.
You get a number, today is 40443 for example (because today is 40,443 days after Jan 1st, 1900), and then your usual date format skills can fix it.

More detail about TEXT here

@jaytkay Please try reading all the responses in the thread before your answer. Excel will not see dd/mm/yyyy as a date value unless you program a custom format. It is seeing it in text format. Also, your little “multiply by one” trick has no effect, as you can’t multiply text by a number to begin with. It doesn’t magically make Excel see text-in-a-format-it-doesn’t-know as a date. Please give it a try and see for yourself.

@CyanoticWasp is exactly correct – my formula is assuming two-digit days and months, and four-digit years.

@JLeslie the reason it doesn’t is that it would have no way of knowing 01/01/2010 through 01/12/2010 (and 02/01/2010 through 02/12/2010, etc) is in dd/mm/yyyy format or mm/dd/yyyy format. So it assumes that if you type ##/##/####, you’re talking about the format relevant to the locale you’re using.

@MrItty Sorry to upset you.

@jaytkay huh? What gave you the idea that I was upset? That I factually corrected your erroneous statements?

There is, actually, a much easier way to do this, although it involves a few simple steps. The advantage is that doing those steps lets you see exactly what’s going on each time, and you’re less likely to make an error.

1. First, add a few columns to the right of the column that has the xx/xx/xxxx input.

2. Select the column with the xx/xx/xxxx dates in text format and select from the Excel command menu Data / Text to Columns.

3. Select the “delimited” option, and in the next dialog box select your delimiting character—whatever character appears between the date numbers.

4. Click ‘Finish’ and you should have three columns of numbers, which (in the example you gave) will be Day, Month and Year. Let’s say those columns are A B and C.

5. In another column enter the formula (much simpler formula!):
=Date( C1, B1, A1)
The formula is entered in Year / Month / Day order, which is what Excel expects regardless of how you choose to DISPLAY the date.

6. Modify the date format (Format / Cells) to show the date the way you want it to be displayed.