In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string?

The use case is that I have data exported from a database that has ISO8601 timestamps (as strings in a .CSV file) and I’d like them to show up as readable dates and times while still making it possible to just paste more exported timestamp data later in those cells.

I’ve searched for a solution and have found other questions about formatting dates (e.g. "Convert columns of ISO 8601 strings to dates in Google Sheets" and "How can I change Google Spreadsheets to have iso 8601 dates as default?") but they're asking for different things.

I'm not looking for a way to parse an ISO date string to a readable string in another cell etc. but a way to present an underlying date in a localized way, hopefully preserving that presentation format when a new date string is pasted into the cell later.

I know one option could be to have this exported CSV data on a different sheet and collect it from there to another "presentation" sheet in a nice, localized way but I'm not 100% sure it'd work, and I'd like to avoid that to keep things simpler anyway if possible.

Based on what I've found so far this may not be possible in Sheets at all but I'd love to be wrong.

  • 1
    Because of different data types, you'll have to convert text to date type in order to format it accordingly. – Oleg_S Sep 23 '20 at 8:37
  • 1
    Difficult MJV. One hack would be to import the .csv file yourself with UrlFetchApp with Google Apps Script, indeed parse it (stackoverflow.com/a/11820304) and possibly add the ISO8601 value as a seperate column or as a note (into the corresponding cell) – Jacob Jan Tuinstra Sep 23 '20 at 8:39
  • Thank you for the comments! I think there's no way to do this the way I hoped, and on second thought that two-sheet model (one for raw data and the other for presenting it) is probably a clean and handy enough way to achieve this. – MJV Sep 23 '20 at 12:09

Your Answer

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

Browse other questions tagged or ask your own question.