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
Solved! Go to Solution.
Tryโฆ INDEX(EXTRACTDATES(INDEX(SPLIT([UID],โdobโ),2)),1)
Could you please try an expression something like
TEXT (
DATE(MID([UID],FIND(โdob=โ,[UID])+5,10)) ,
โdd/mm/yyyyโ
)
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.
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.
Also you may wish to elaborate the above statement a bit more. AppSheet supports different locales.
Please take a look at the following articles-
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.
Nice point as usual on date format @Aleksi with respect to dates and months having single digits.
TEXT(DATE([UID])),โdd/mm/yyyyโ)
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)
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.
Tryโฆ INDEX(EXTRACTDATES(INDEX(SPLIT([UID],โdobโ),2)),1)
Thanks it worked
one more question does appsheet have any function to parse xml ?
Nope.
Iโm afraid no.
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.
Soโฆ you want to convert โyyyymmddโ string to a real date?
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.
Please change the orderโฆ
DATE(MID([dateStr],5,2)&"/"&RIGHT([dateStr],2)&"/"&LEFT([dateStr],4))
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.
Go to www.appsheet.com/sampleapps and search with โDashboardโ
User | Count |
---|---|
40 | |
36 | |
33 | |
23 | |
17 |