The date format changes according to the view

a small bug for the display of dates:
I create a table with a date field in French format (dd/mm/yy).
When I switch to a Cards view grouped by categories, the date is in American format(mm/dd/yy) !
Amazing !

As far as I know, it’s always been like this I think …
I’ve always thought, when using views, that the personal settings/configuration of a field, we’re going back to “default”.

I found myself using FormatDateTime() more and more lately, to convert dates into text columns that survive copying over to spreadsheets and issues like OP had mentioned here.

1 Like

Thank you very much for this tip.
I’ll try it right now…

I tried it on.
It works very well.
Thank you again!

why can’t we find the formula FormatDateTime() in the help on formulas?

It’s because it’s a “hidden formula” users found :wink: .

Here’s the help: both parameters for dateFormat and timeFormat are integers that correspond to a format type (loosely based on the list one can find in Date/Time column settings dialog). Use the value 0 to not display the date or time part. Try different integers to find the format you need. E.g., the one you need is Date.FormatDateTime(11, 0) for dd/mm/yyyy or .FormatDateTime(7, 0) for d/m/yyyy

Maybe this is a stupid question but why not use Format() ?
Does FormatDateTime()have advantages ? (except the fact that it is quickier and easier to use ?)

Sorry, I’m just curious :slight_smile:

Format is good for putting multiple values into a string, e.g. Format("Deal {1} - Phase {2}", thisRow.[Deal Number], thisRow.[Phase]), but otherwise it’s pretty dumb. Most it can do (unless I’m missing something) is format numbers to this many digits, as docs say here:

Yeah, technically you can recreate a date string with a formula like this
Format("{1:0000}-{2:00}-{3:00}", Today().Year(), Today().Month(), Today().Day())

But why if FormatDateTime, though experimental, does the job much faster and in alignment with format options found in the UI? Extracting Year(), Month() or MonthName(), and Day() to put them in a formatter just feels like smelly code to me (I’m coming from Java background; for me this is like using a very dumb version of String.format() instead of a native DateTimeFormatter class).

1 Like

Thank you very much for all these info :grin:!
My curiosity is more than satisfied :yum::wink:!

For now, I’ve been cautious with the hidden/experimental formulas in case they broke … But I might go there soon and explore those a little bit more :blush:

Hi, just wanting to bump this up, it’s quite frustrating to have extra steps for non-American date formats. While using Date.FormatDateTime(11, 0) does resolve this somewhat, having to have additional columns just to get around it feels unnecessary.

Would be great if this aligned with the Date/Time settings by user automatically.

1 Like