Troubleshooting: Dates are imported as text

If a date in the source data is in a custom format and contains String values (i.e. day of the week (Mon)), it is saved as a String, not a date:

  1. Thursday, January 14, 2021 - Ok.
  2. 2021-01-06 - Ok.
  3. 10-Jan-2021 - Ok.
  4. 2020-05-12 (Tue) - not Ok.

There are several approaches to follow:

1. Do not set up "2020-05-12 (Tue)" custom format in the source file, use the date format without a day of the week instead (we tested "yyyy/mm/dd" and "yyyy-mm-dd" and both work fine). Set up the needed date format in the destination file:
e5062993bdb58ec079c9ce0a60217ece.png
2. Keep import as is and apply a function on the left to get date values back:
94377d1cd561ca0a76aef715bbfeb33b.png

b7108475a32aaa598795eabb601dc88d.png

Please read this article to learn more about how to keep your formulas.

Formula example for this custom data format : =DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.