Hello,
I'm facing a challenging issue when reading dates from Excel files using readexcelsheetpaging(). The problem occurs when Excel cells contain dates in custom format that appear as values like "2026-07-17".
readexcelsheetpaging()
The Problem: When Appian reads these date values, it interprets 2026-07-17 as a mathematical expression (2026 - 07 - 17 = 2002) instead of treating it as a date string. This happens before I can apply todate() or text() functions.
2026-07-17
todate()
text()
Context:
Excel cells contain dates with custom formatting (cannot be changed on Excel side)
Dates appear as format like "2026-07-17" when read by Appian
Need to convert these to proper Appian date values
Current Code:
a!localVariables( local!document: ri!document, local!sheetNumber: ri!sheetNumber, local!startIndex: ri!startIndex, local!tempResult: readexcelsheetpaging( excelDocument: local!document, sheetNumber: local!sheetNumber, pagingInfo: a!pagingInfo( startIndex: local!startIndex, batchSize: ri!batchSize ), ).data, local!result: a!forEach( items: remove(local!tempResult.values, 1), expression: a!forEach( items: fv!item, expression: if(fv!item = "NULL", null, fv!item) ) ), a!forEach( items: local!result, expression: a!localVariables( local!compliance: rule!getComplianceByID(id: fv!item[12]), 'type!{urn:com:appian:types:AD}AD_ENGAGEMENT'( id: null, fiscalYearEndDate: todate(totext(fv!item[11])), compliance: local!compliance, complianceDueDate: todate(fv!item[13]), inputDate: todate(fv!item[14]), reviewDate: todate(fv!item[15]), companyDeliveryDate: fv!item[18], electronicSubmissionDate: todate(fv!item[21]), receiptProcessingDate: fv!item[22], createdBy: "ADMIN", createdOn: now(), modifiedBy: "ADMIN", modifiedOn: now() ) ) ) )
What I've Tried:
Using todate(fv!item[index]) directly → returns null/empty
todate(fv!item[index])
Using todate(totext(fv!item[index])) → still issues with mathematical interpretation
todate(totext(fv!item[index]))
Various combinations of text() and todate() functions
Expected Result: Convert Excel date values like "2026-07-17" to proper Appian date objects.
Question: How can I properly handle Excel dates in custom format to prevent Appian from interpreting them as mathematical expressions? Is there a way to force Appian to treat these values as strings before applying date conversion functions?
Any insights or alternative approaches would be greatly appreciated!
Thanks in advance for your help.
Discussion posts and replies are publicly visible
You can refer the code below to convert date from a date stored as text - as long as you are always getting a consistent yyyy-mm-dd format from the excel.
If you have inconsistent formats then depending on format some adjustments will need to be made to retrieve the date. Hope it helps!
a!localVariables( local!dateText:"2026-07-11", local!temp:split(local!dateText,"-"), date(local!temp[1],local!temp[2],local!temp[3]) )
Thanks for your answer. The problem is that the Excel cell(s) is not read as Text string but as a number (e.g. 2026-07-11 = 2008 as integer and thus Appian works with the 2008 instead of the previous one with the yyyy-mm-dd format ). I can't find a workaround to that
oh okay! We cannot decipher date from 2008 because 2008 can be 2026-07-11 as well as 2025-11-6. We need to read the data as is from excel itself then transform if needed.
Alessandro Tolli said:Excel cells contain dates with custom formatting (cannot be changed on Excel side)
When you download the excel then can you see data as "2026-07-17" ? Also as you mentioned cannot be changed on excel side, can you check the column type in excel which stores these date if its Number or General etc.
Ensure Excel date columns are formatted as Text before import.If you get only numbers like 2008, you cannot reconstruct the original date in Appian.Change Excel Column Format to Text1)Select the column or cells you want to convert.2)Right-click on the selected area, then choose Format Cells...3)In the Format Cells dialog box, go to the Number tab.4)Select Text as the category.5)Click OK.
Now, any new input in those cells will be treated as text strings.
If I open the Excel and set the format to text I'll go from "2025-07-11" to something like 44698. So I guess my only choice is, once I've the Excel locally, find and replace the "-" element into something else so that the cell is read as text and not a math function
Ideally it should be a Date column in excel. It should not be text as text will indeed change it to a number. If its a text then it should be within quotes (""). If you can make it date column in excel then read the function should read it as date as well. E.g. I uploaded this excel where first is 'Genera' and in quotes while other one is in 'Date'' type column. You can see I am able to get proper output from both of them. So if you can change in Excel have the date column configuration applied to your columns where applicable.
Could you please clarify how you receive or generate the Excel file containing the dates?Is the Excel file created or exported automatically from another system, or do you receive it via email from someone else?Do you have direct control or access to modify the format or content of the Excel file at its source before importing it into Appian?
thank you both, i managed to find a solution. I passed as input the given dates and now it seems to work if I set the rule!input type to Text (although on Excel the format is Custom). Having a text input i can use the left/mid fx to extract the date from it
Great to know that!!If possible could you post your expression here.
Sure thing.
1. I created a rule:
if( a!isNotNullOrEmpty(ri!dateTimeText), date( tointeger(left(left(ri!dateTimeText, 10), 4)), tointeger(mid(left(ri!dateTimeText, 10), 6, 2)), tointeger(right(left(ri!dateTimeText, 10), 2)) ), null )
then I set the ri!dateTimeText as Text,
then I used the type! passing the whole thing:
a!forEach( items: local!res, expression: 'type!{urn:com:appian:types:AD}AD_SAMPLE'( id: null, dateinput: rule!convertDateTimeToDate(dateTimeText: fv!item[14]), datereview: rule!convertDateTimeToDate(dateTimeText: fv!item[15]) ))
Now the dates are correctly read as Date type. I was just passing the information as a fv!item[17] and so on, using a rule input set as text bypassed the math subtraction on the cell and thus I was able to split the information using mid and left fx. Thank you all for your replies