No…!? whay??
Sometimes that will be added in by the spreadsheet in order to differentiate text from dates.
Hmmm…
we do not have that in any of them… not in one that works in the app also.
Okay, try and change the whole column to text type - then back to datetime (in the spreadsheet)
same thing nothing has changed
Yeah not sure. I’ve had this problem before, and I did figure out a way to fix it - I just can’t remember what I did.
it is a bit frustrating, and it seams it has no solution, date time is there but the app wont read it… now we know why… but it seams it can not be fixed…
@Phil any thoughts?
Never mind, we managed to figure it out.
Turns out it was all about massaging the old data, because indeed @Steve was right: the problematic datetimes were actually stored as text in the back end.
The problem was that the Time portion that was saved had a period separator, but gsheets needs a colon separator.
I put together a concatenate to format the text in the correct way:
=iferror(concatenate(left(D262; find(". ";D262)); " “;substitute(right(D262;find(”. ";D262 )-3); “.”; “:”)); “”)
Then I copied the result of this formula, and pasted the whole column over the original one - but doing a special paste where you only paste the values, not the formulas, because this then deposits the data into the column in the correct format for the spreadsheet to interpret as a date time.
The final step was to re-format the data of the column into a datetime.
Phew!
@MultiTech_Visions
Best guy ever…
Big thanks to you…
You’re very welcome.
Well done!!!