0

I have a GoogleSheet that's been working fine using the IMPORTXML function, until recently.

It seems that when the function imports XML data that are in ISO date and time format that it changes the format.

For example: importing "2021-12-20T00:00:00" becomes "12/20/2021 0:00:00"

That is:

YYYY-MM-DDTHH:MM:SS

becomes

MM/DD/YYYY H:MM:SS

Since the worksheet has been using the MATCH function, the GoogleSheets imported value will not match the XML code's value.

How can this be resolved so that GoogleSheets imports and processes the data in "YYYY-MM-DDTHH:MM:SS" format as it did before?

Improve this question

1 Answer 1

Reset to default
1

It's very likely that Google Sheets is identifying as a date-time value and displaying it using the default date-time format for the spreadsheet region that your spreadsheet is using.

You could use ISDATE, ISTEXT or other ways to verify if the value is date / text and set your formula to use the appropriate value to do the match, i.e. you could use TEXT(value, "YYYY-MM-ddTHH:mm:ss") to convert the date into a value having the formatting that your formula requires.

Related

Improve this answer
5
  • Do you when the app, GoogleSheets, adopted this process of applying the user's regional default date-time format rather than the imported XML data's format? The formulas had worked fine until recently.
    – Catalyx
    Jan 19, 2022 at 22:00
  • In general, this was the default behavior but I don't know about the specific case of IMPORTXML... Are you sure that nothing have changed in the way that the XML files are being generated?
    – Rubén ♦
    Jan 19, 2022 at 22:09
  • I'm almost certain that the XML format hasn't changed since the source XML data are from a US government website that's regularly queried from around the world. I was curious when the changes took place since it may require my changing old worksheet queries to comply with this default format in effect.
    – Catalyx
    Jan 19, 2022 at 22:18
  • 1
    Better to look if they have a place to notify changes, you might be surprised.
    – Rubén ♦
    Jan 19, 2022 at 22:28
  • There doesn't appear to be recent changes in Google Sheets's announced release changes. Thanks though.
    – Catalyx
    Jan 19, 2022 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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