Importing Dates from google sheets show as 5 digits

I believe the google sheet documentation says this is an “epoch” date format. how do i get grist to recognize it as a date?

Which column type did you define on Date ? With a fresh document, I can’t reproduce the issue. Or would you have a sample to share on ?

Here is the doc:

ive tried changing the type and no luck. this is coming from a n8n workflow .

Your Date column has a Text column type. You should try changing it to Date. That said, the “epoch” format they use seems weird. Would you have the real date of the first one, please ?

When i change the grist column type to Date it just turns red:

Here it is from the original spreadsheet:

so 45119 = 7/12/2023

from google:

I hadnt thought of it before but the contents of the google sheet is an arrayformula that im using to “flatten” a sheet of pivoted data. im going to just copy the values and try that to rule it out.

I tried importing it again, this time with just values and still no luck with the date format. It did resolve a weird error that was causing extra records to be pulled in.

So given the definition of the epoch, you might have an epoch column (that you may hide), automatically imported from your google sheet, and then a Date column defined as the following formula:

from datetime import date, timedelta
date(1899, 12, 30) + timedelta(days=$epoch)

Sample here with a subset of your data.

1 Like

thanks again! you are an asset to this forum and the product.