Solved: Date formatting - Text to Date

SenthilD · 05-11-2020 02:35 AM

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

Aleksi

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

View solution in original post

Suvrutt_Gurjar

Could you please try an expression something like

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

SenthilD

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.

Suvrutt_Gurjar

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.

Suvrutt_Gurjar

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

Please take a look at the following articles-

Aleksi

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.

Suvrutt_Gurjar

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

NL_CN

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

SenthilD

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"?>

Aleksi

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

SenthilD

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

Aleksi

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

SenthilD

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

SenthilD

Aleksi

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

SenthilD

Thanks it worked

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

Steve

Nope.

Aleksi

I’m afraid no.

Jerry_Fernandez

Hi Aleksi. I have the same inquiry… what if the text string that I needed to change into a date is yyyymmdd format? The formula I use is always expression acceptable but kept on producing errors when testing.

Aleksi

So… you want to convert “yyyymmdd” string to a real date?

Jerry_Fernandez

yes… the string yyyymmdd, I plan to do a column for date, week and month. I was able to source some formula in some topics ( DATE(CONCATENATE(RIGHT([dateStr],2), “/”, MID([dateStr], 5,2),"/", LEFT([dateStr],4))) ) however, it always resulted to errors.

Aleksi

Please change the order…
DATE(MID([dateStr],5,2)&"/"&RIGHT([dateStr],2)&"/"&LEFT([dateStr],4))

Jerry_Fernandez

oh sorry… didn’t notice that its date first. While you here, can you give me a good app to review for a dashboard? Thank you, Aleksi.

Aleksi

Go to www.appsheet.com/sampleapps and search with “Dashboard”