Convert date format from US to EU

Tony Oxa

I need to convert a date from a USA format (MM/DD/YYYY) to a EU (DD/MM/YYYY) without changing the region in my settings.

Danielle Arteaga

If you do NOT need the EU date column to be a "date" type column (e.g., it is a text/number column), then this will work:

=DAY([US Date]@row) + "/" + MONTH([US Date]@row) + "/" + YEAR([US Date]@row)

[US Date] should be replaced with the name of the column holding the USA date to be reformatted.

If you need it to a be a "date" type column, I believe the only way to accomplish that is to change your region settings.

Tony Oxa

I tried the formula as you advised and it returns the invalid operation error. The column that i am writing the formula its not a date column.

Genevieve P.

Hi @Tony Oxa

You can do this using the Date Format option in the tool bar of your sheet.

I'm based in the UK but I collaborate with users in the USA. On our shared sheets, I always change the formatting to have the month written in text for clarity, like so:

Here's more information: Apply standardized date formats in your sheet

Cheers,

Genevieve

Tony Oxa

I can't use the date format option, i need the dates to be converted by a formula, i have users using it from different regions.

Genevieve P.

Hi @Tony Oxa

The date format in a sheet only impacts the visual display in the grid. Formulas look at the underlying value, so you can use DAY, MONTH, YEAR, and DATE functions that look at the date while having a different format applied over top. 🙂

If you'd prefer to use the formula above, this should work for you as long as you are referencing a cell that already contains a date. If you reference a cell that is blank or has text, you'll get an INVALID error as there's no date to look at. Try wrapping it in an IF statement to help:

=IF(ISDATE([US Date]@row), DAY([US Date]@row) + "/" + MONTH([US Date]@row) + "/" + YEAR([US Date]@row), "No Date")

Cheers,

Genevieve