Any sane way to deal with DATES? CONCATENATE, TEXT, DATE and such

[integraOdontoSj]

Ok, let me explain. In my project, I often need to construct DATES for specific invoices or parcels, that repeat themselves. (ex: first due date to 05/10/2024 - dd/mm/yyyy format -. The next parcels or invoices should be exactly at the 5th day of the next month. 05/11/2024, 05/12/2024 and so on.)

One way to do it is with TEXT(CONCATENATE(date, [format])). Problem is, sometimes you cannot convert a date to string and run through the CONCATENATE, and thus you don't get the desired format. What the appsheet specialists team told me to do is to creat a column as TEXT and not to use the TEXT to wrap everything up.

So... We create a column and set it as TEXT, using CONCATENATE() to create the date.

Yes, that works, BUT the CONCATENATE() function, although appsheet DOES NOT say in its documentation, gives the result of the expression as MM/DD/YYYY format. If you're like 90% of the world and use DD/MM/YYYY, good luck using that.

Here's a screenshot of a virtual column designed to convert the TEXT from the concatenate() function to DATE, so I can use it to determine if a parcel is due to payment or not:

although the text format of the concatenate worked perfectly, appsheet insists in seeing it as MM/DD/YYYY.

I'm frustrated to the core, and I don't mean to be an ah, but working like this is the very definition of insanity.

There's no way to set the default date format to the project, or to know what the platform is doing underneath. The fact that in the expression helper there's absolutely no info about CONCATENATE() changing formats makes it even more frustrating to work with. 

Of course, it can always be that I'm the idiot, so I kindly ask, to those who actually know how to navigate this:

Is there a sane way to deal with dates in this platform? What would be a good practice or alternative for those who are learning and stumble across the same thing I did in this example?

Almost every single ticket I open goes to a specialist that explains absolutely nothing and answers after I've found a workaround the issue, not helping at all, or not giving enough info to actually explain what's goin on so I won't need help again later.

[Vincent_P]

Hello, 

i'm not a specialist 🙂 but I try to help people 🙂

first check, if you have everywhere the same locale (application, table, datasource, ...) and then I personnaly have column typed as date for working with formula and computed column as text just for format the result if I a have a problem like it.

[MultiTech]

You're so close, go look at the documentation for text()

The second parameter for the text() function allows you to specify how you want your temporal data type to be displayed. It gives you absolute control over every part of how the date is shown, giving you the ability to add in any other elements inside there, special characters like periods and slashes and whatever, to help make your formatted date exactly how you want it.

Text([date_column], "mmm DD, yyy")

[MultiTech]

---

@integraOdontoSj wrote:

Almost every single ticket I open goes to a specialist that explains absolutely nothing and answers after I've found a workaround the issue, not helping at all,

---

Sounds like you might benefit from working with Appster instead:

https://chat.openai.com/g/g-ZG9TkPOrl-appster