0

enter image description here

I want to convert mm/dd/yyyy to yyyy/mm/dd with function on Google Sheets.

I applied TEXT function like the picture but it's not working. How can I convert this?

2
  • Using TEXT() function converts it to string and you won't be able to use it as a date anymore is you need. Try Format\Number\Custom date and time format where you can choose from premade formats or make your own. This way it will still be a date and you can use other functions on it.
    – Oleg_S
    Apr 18 at 11:14
  • Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet. Apr 18 at 12:12

1 Answer 1

1

Since text() is returning the value without change, chances are that the value is not a numeric date but a text string that just looks like a date. Set File > Settings > Locale to the real locale you want to use, and re-enter the dates in local format.

If you have many such dates to convert, use a formula to convert them, like this:

=to_date(value(regexreplace(trim(D28), "(\d+)/(\d+)/(\d+)", "$3-$1-$2")))

Format the resulting dateserials as Format > Number > Date or another date format of your choosing. See this answer for an explanation of how date and time values work in spreadsheets.

4
  • hi, I did not understand this part of your calculation: "$3-$1-$2" also because I get the same result by modifying it or placing it empty. Could you kindly explain it to me? (test sheet at: docs.google.com/spreadsheets/d/…
    – Daniele
    Apr 21 at 17:02
  • $3-$1-$2 signifies the third, first and second capturing group, respectively. See regular expressions. Apr 21 at 21:20
  • Your test spreadsheet is in the United States locale that uses m/d/yyyy by default, so the dates there are already interpreted correctly. Your question suggests that your real spreadsheet is in another locale such as France, where that format will result in many dates entered as becoming text strings that just look like dates. The formula will convert such text strings, and incorrect numeric date values, into a date, assuming that those incorrect values should be interpreted as d/m/yyyy. Apr 21 at 21:20
  • i got it, thank you very much
    – Daniele
    Apr 22 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.