I’m a Czech. I have a table of people with their dates of birth. Those dates are in format d. M. yyyy, which is common in Czechia. Unfortunately, Coda imported my Date column as text, not as date, and if I set the column format to date, I get an error Cannot convert data to specified format. Well, that’s quite a bummer for non-English users.
So how to convert imported date strings into proper date values?
Change the date to US format first, then change back into your own once in Coda
I’m bumping this again up top and adding my specific issue. My function below might help others but something is wrong, it’s not there yet.
So I have a table with European format dates.
I am referencing this data in another table and trying to reformat them into American format so they can be displayed and filtered correctly.
I can get them to display correctly but then filtering by date just doesn’t work. I am confused.
Here’s my function to convert the date. I am getting a Wrong argument type error, is that why I can’t filter. Using ToNumber() doesn’t help, error still says “…which is text”
In any case, the dates display correctly but then if I try a filter like [Trans Date]=ToDate("2/4/19"), it doesn’t work.
Any thoughts?
EDIT:
Just figured out that if you add ToText() on the date when filtering, it works…!? Now I am more confused, because both of these work:
[Trans Date].ToText()="2/4/19"
[Trans Date].ToText()>="2/4/19"
Why would filtering using a Date object doesn’t work but when comparing strings it understands which is larger or smaller? 
Not sure precisely what you mean by ‘European format’, there’s a few different formats in use in Europe… are you referring to dd/mm/yyyy?
If they’re all in the same format, can you do a variation of the below:
Date([Trans Date].Split("/").Nth(3).ToNumber(), [Trans Date].Split("/").Nth(2).ToNumber(), [Trans Date].Split("/").Nth(1).ToNumber())
?
Hey Jow,
Thank you for taking the time to help. Yes I meant DD/MM/YY. 
Sorry didn’t know there were multiple formats within Europe.
I am essentially using Slice to achieve the same result as you. But then the column cannot be sorted even though clearly Coda understand the date. I must be doing something wrong but I cannot figure it out.
See my screenshot below and how the filter shows 0 of 37 rows when it shouldn’t. I formatted the date column as “MMM DD, YY” for clarity. Any ideas?
Yep. in Czechia, we use d. M. yyyy format, and I think Coda should support such date formats, if it plans to reach areas out of US
Welcome. Can you try rewriting the date string you have there as ToDate(“2018-11-11”)? I think Coda is struggling to interpret your filter condition rather than just your column.
If that works, then we can work out where the filter condition is coming from and simplify it until it works nicely (maybe a control would be good here)
Joe
Hey Joe,
Appreciate the advice. I tried rewriting the string, it doesn’t work.
Thing is, Coda suggests the original formatting I used. See screenshot below.
As you say, I want to add a control to filter the dates but I can’t even get basic filtering to work.
Have a look at the screenshot below: if I change to “less than” the 1900s, it shows all rows. 
What is going on here? The [Trans Date] column is obviously understood by Coda as date because I can format it. Have I come across a weird bug?
You have a sample doc you can share?
Would help debugging.
Have you tried creating a second column which is just a static date, and then comparing columns rather than a string converted to a date? Does that work?
Joe
Hey Joe,
Great idea about the static date, I thought there would be some progress but it’s the same, I can’t filter properly. I must be doing something wrong. The thing is that when I try to
Here’s a sample doc, with only the necessary data. I created the Plain Date as per your advice.
Thanks again!
the millenium bug strikes again!
Your dates are being recognised as 18 AD, not 2018 AD.
Change your Trans Date formula to the below and you’re golden:
Date(Concatenate("20", thisRow.Reference.Date.ToText().Slice(7,8)),thisRow.Reference.Date.ToText().Slice(4,5) ,thisRow.Reference.Date.ToText().Slice(1,2))Another way to rewrite from EU dates (DD/MM/YYYY) to US formats (MM/DD/YYYY) is to use the RegexReplace formula: Input.RegexReplace("(\d+)/(\d+)/(\d+)", "$2/$1/$3").ToDate()
This will match a value <number1>/<number2>/<number3> and rewrite to <number2>/<number1>/<number3>. Our story for localization support is spotty at best and needs a lot of work.
Hope this helps,
Nigel.
Yup! I’m rubbish at Regex, your solution is much more eloquent.
However, it’ll still cause problems if your date is formatted as DD/MM/YY, so don’t forget to pop in ‘20’ in before the $3:
Input.RegexReplace("(\d+)/(\d+)/(\d+)", "$2/$1/20$3").ToDate()
No way! That’s hilarious, I don’t think I would ever have thought about it.
Thank you so much Joe, you made my day.
Thanks for the alternative solution. I need to learn RegexReplace, I haven’t used it at all. 
Really appreciate the honesty about localisation. << written correctly
I don’t blame you guys, you have to pick your battles. Keep up the good work.