Appsheet TODAY() formatting

[Viktor]

I have an issue with the way AppSheet formats a date with the TODAY() function.

I use TODAY() in a workflow in order to create a new folder based on the current day, in OneDrive. Here, OneDrive seems to format the date automatically to DD-MM-YYYY i.e. 24-02-2020. This is fine.
I also use TODAY() to create a filepath to the same folder, but as this is stored in the spreadsheet AppSheet seems to work with (D)D.(M)M.YYYY i.e. 24.2.2020.

I fixed the dots by using SUBSTITUTE to replace dots with dashes but then I still get 24-2-2020, when I need it to be 24-02-2020. I guess I could use SUBSTITUTE() for this as well, but then I’d have to make a case for all the days and months where I need to add a 0, no?
The spreadsheet is in Excel, but the formatting doesn’t seem to happen on the spreadsheet level, at least not at a quick glance.

Any help is appreciated!

[Bahbus · Accepted solution]

I believe default date formatting is for AppSheet is determined by the locale of the device, and that’s it. But luckily for you, you can just wrap TODAY() in a TEXT() formatting.

TEXT(TODAY(),"DD-MM-YYYY")

[Steve]

As @Bahbus notes, locales play a big role in how dates are presented. I agree with him that your best bet is to wrap TODAY() with TEXT() to generate a date in exactly the format you want.

[Kirk_Masden]

Also, if you would like to show text for the month (e.g. “FEB” instead of “2”) check out the following:

help.appsheet.com

INDEX()

One specific list item

INDEX(
  {
    "January", "February", "March",
    "April", "May", "June",
    "July", "August", "September",
    "October", "November", "December"
  },
  MONTH(TODAY())
)

This could be enclosed in a CONCATENATE() expression. You can even use IF() to format the date differently, depending what other values will be shown with it.

[Bahbus]

Kirk_Masden:

INDEX( { “January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December” }, MONTH(TODAY()) )

Or also TEXT(TODAY(),"MMMM")

[Kirk_Masden]

Thanks! I had missed that. Good to know!

The “index” and “concatenate” approach has the advantage of making it possible to accommodate just about any language (as far as I know, at least) without depending on existing formats. Here’s something I just did on my app:

[Maris_Kanders]

Just where?- In which field it should be written to be right. -In “App formula” field?, -“Initial value”? -“Suggested value”? . In these fields it shows error. Sowhere else?

[Kirk_Masden]

Hi! I’m not sure I know what you mean but I usually make a virtual text column and then format the date there.