Hi
We have just upgraded the Appian version from 21.4 to 23.3 and we have an issue with the date format exported to excel coming from record types, in the prior version this issue was not happening, does anyone has experienced the same issue?
21.4 version-correct format
23.3 version - incorrect format
by looking the format in the excel is taking it as if it was a text but this is the logic behind the date column:
if( a!isNullOrEmpty(fv!row['recordType!{92d1b022-5508-4130-83d7-528677bb837e}ACH CASE.fields.{b79811e6-b6ad-4b38-b04d-134b4d22d656}createdOn_dateTime']), todate(null), todate( text( fv!row['recordType!{92d1b022-5508-4130-83d7-528677bb837e}ACH CASE.fields.{b79811e6-b6ad-4b38-b04d-134b4d22d656}createdOn_dateTime'],"mm/dd/yyyy hh:mm AM/PM" ) ))
we are just converting the date and time to date and when we do that the format gets reflected as text in the excel which was not happening in version 21.4
Discussion posts and replies are publicly visible
I do not full understand. You say that when the Excel only shows the year, that's what you want, but the code you posted clearly returns a full date & time.
Second, the code
pedro.arenas said:todate( text( fv!row['recordType!{92d1b022-5508-4130-83d7-528677bb837e}ACH CASE.fields.{b79811e6-b6ad-4b38-b04d-134b4d22d656}createdOn_dateTime'],"mm/dd/yyyy hh:mm AM/PM" ) )
does not make much sense to me, as it converts a datetime into a string and back into a datetime.
Can you help me understand what you what to achieve?
Yes,
The format of the date was showing ok prior the upgrade to 23.3 we were working on 21.4 version and the date format exported in the excel was like this:
This was working fine in 21.4 by using the same logic that I just included in the above post, I tried doing the followig;
todate(fv!row['recordType!{92d1b022-5508-4130-83d7-528677bb837e}ACH CASE.fields.{b79811e6-b6ad-4b38-b04d-134b4d22d656}createdOn_dateTime'])
as the objective is to only show the date not the time and show it as in the above image.
Currently the format it's getting reflected like this
Can you unzip two Excel files, old and new, and check the XML content for how exactly the values are exported?
Excel is known for doing all kinds of "magic" things to data, and we need to compare the raw exported data.
Hi Stefan,
This is what I found in the worksheets.xml
correct date format
incorrect date format
I have found a solution to this problem, I just added a new custom field to extract partial dates to my record type and it worked.
Interesting. For me, none of these values is a valid date or datetime.