Convert date format from US to EU

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.

Answers

  • Danielle Arteaga
    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!