Date formatting - Text to Date

Hello,

For a table, the input date is from a text is received as “dd/mm/yyyy”. As appsheet expect “mm/dd/yyyy” format, I’m using a text function as below.

TEXT (
MID([UID],FIND(“dob=”,[UID])+5,10) ,
“dd/mm/yyyy”
)

But its throwing a an exception saying that TEXT function with two arguments requires a temporal type and a format string

Please help.

Regards,
Senthil D

Could you please try an expression something like

TEXT (
DATE(MID([UID],FIND(“dob=”,[UID])+5,10)) ,
“dd/mm/yyyy”
)

1 Like

Thank for your quick response. The formula accepted as a valid one!.
But it didn’t result any output.

The QR code returns date string as “15/04/1947”. But appsheet excepts mm/dd/yyyy format.

1 Like

Thank you.

I suggested to wrap it with DATE() so that the first part of the overal expression becomes temporal ( time, date etc.) etc. This has eliminated the basic syntax error, you mentioned earlier.

You may want to share what is the [UID] column consisting of and its type because the expression is built around the [UID] column.

3 Likes

Also you may wish to elaborate the above statement a bit more. AppSheet supports different locales.

Please take a look at the following articles-

2 Likes

The result from the [UID] needs to have the syntax in correct order like mm/dd/yyyy. Otherwise the TEXT() can’t find the date to format (for example with 15/4/2020). Also… if you use MID() with the “10”, it’s possible that it won’t work for example with the string 1/1/2020 because it contains just 8 digits.

3 Likes

Nice point as usual on date format @Aleksi with respect to dates and months having single digits.

Thank @Aleksi @Suvrutt_Gurjar for your help.

Given below the date captured by QR code reader and assigned to UID [text] and tokenising the string to get the required data. The MID function tokenised the string and extracted the date field as “15/04/1974”. But the DOB is defined as “date” field and didn’t accept.

The locale also set as English(India)

Please advise.

<?xml version="1.0" encoding="UTF-8"?>

Try to use INDEX(EXTRACTDATES([UID]),1)

2 Likes

I tried but didn’t work. If I use this function, I’ve wrote MID functions to get other data fields like first name, father name, city, pinched also not working.

Thanks in advance

Are you able to show one sample string what it actually is? It would be easier to find the solution.

1 Like
<?xml version="1.0" encoding="UTF-8"?>

1 Like

Try… INDEX(EXTRACTDATES(INDEX(SPLIT([UID],“dob”),2)),1)

4 Likes

Thanks it worked :clap: :slightly_smiling_face:

one more question does appsheet have any function to parse xml ?

1 Like

Nope.

I’m afraid no.

TEXT(DATE([UID])),“dd/mm/yyyy”)