Date with leading zero

[Fabian_Weller]

I would like to see Date values with leading zero.
Out of the box, AppSheet shows a date value like this: 1.1.2022
But I want it like this: 01.01.2022

So I made a VC with type Text and the expression:
TEXT([Date],"DD.MM.YYYY")

This is perfect. But when I sort the table by this VC, the sort order gets bad.

Sort by [Date]

Sort by [DateVC]

Des anyone know a workaround?

[Craig_QREW]

Hello, so looking at this the reason why the “Sort by [DateVC]” is giving you an odd order is because we as people look at this and see a date. However appsheet is looking at this and seeing it as a text. So this is in order of the numbers descending. What I recommend is make two columns one of them would be a column that you would show. [DateVC] in your UX views anytime you display the date use that one. Then I would make the [Date] the one appsheet understands in any formula or sorting.

So [DateVC] anytime you want to display the date in your app.
[Date] in a format you do not want to see can be used only in formulas because it keeps the data type of date.

I think that should resolve your problem but please let me know if I can be of anymore help.

Craig
QREW Technologies

[jyothis_m]

Pls check if this is useful

Change Date Format from D/M/YYYY to DD/MM/YYYY Questions

Hi, In my App, I am having Table view in which the date column displays date as 7-1-2022. But i would like to display it as 07-01-2022 (with zero in beginning for single digit) Is there any way to do this directly in this column itself without going for Virtual Column or Text() Function. One thing I found is that in Browser its displaying as 07-01-2022 but in Mobile it is 7-1-2022. Also is there any option to change the separator between day,month,year. [1642178316265]

Final solution i found was to change the Language Setting in Android to English UK

[Joseph_Seddik]

I faced the same issue. My workaround was to start with the year as in: yyyy:mm:dd.

Having a two-digit display was a user request. When I turned the date around, he didn’t even notice and was happy with it.

[SkrOYC]

Hi @Fabian
I just can confirm basically what others have said.
The format without zeros depends on your locale config so we can’t change the standars.
You found the great TEXT() expression to be quite good at displaying it the way you want, but it’s just text now.
I use the YYYY/MM/DD technick when I need a string of text to be interpreted from old to new since it will work almost on every place you can imagine.
For example, I always save files and create them using YYYY-MM-DD at the begining because I can then sort by name on my files explorer. Since the created/modified date could change or lost (it’s just metadata) the name works best for me

[Fabian_Weller]

Thank you all for the good input.
I will go with TEXT([Date],"YYYY-MM-DD")

[Marc_Dillon]

That is a good choice. I also approve of ISO date format YYYY-MM-DD.

[jyothis_m]

I was able to do sort with Date Type that too dd-mm-yyyy format itself without going for TEXT() after implementing the setting change in android as i mentioned above.

in this if i click the date header it sorts according to date.

[Fabian_Weller]

Thank you for this update @jyothis.m but asking the customer to change it’s language settings in android is sometimes too much

[jyothis_m]

Fully agree on that. Just posted it in case if its useful.

[Steve]

Fabian:

I would like to see Date values with leading zero.

Why?

[Austin]

all your values in the column would be the same width then

[Steve]

Not unless you use a fixed-width font, too.

[Fabian_Weller]

I should have said: My customer wants to see Date values with leading zero. It’s about aesthetics.

I thought it would be as easy as you can set it up in Google Sheets.

[Austin]