Date format issue in excel report

akshayan

Certified Lead Developer

over 7 years ago

We are using "Export SQL to Excel" smart service from "Excel Tools" plugin to create an excel report. Even though we have set a column to date format (column data type is date as well, database is Oracle) in the excel template, the generated excel does not retain the date format. Is there a way to force excel recognize the cell values as date for a column?

Discussion posts and replies are publicly visible

0 Sally Mason
Appian Employee
over 7 years ago

Are you able to provide an example of the format you are seeing versus the expected result or example?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 akshayan
Certified Lead Developer
over 7 years ago

Yes, please refer to the attached. Take a look at the filter at the top (column 2 has dates entered manually and filter works fine, column 1 has values generated using smart service and filter does not work), I tried using different date format like mm/dd/yyyy or dd-MMM-yyyy, but that did not help.
Date Issue.xlsx
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
+1 Sally Mason
Appian Employee
over 7 years ago

Thanks, I can see what you mean. The easiest way to resolve this is in Excel:
1. ***Remove Filters
2. Select the entire column contain the dates you wish to format
3. Press "Ctrl+h" to open "Find/Replace"
4. Replace all "-" with a " "(a single space) and click "Replace All" - this will only affect the selected column.
5. With the dates still selected, press "Ctrl+1" to open the Format dialog
6. In the Format Dialog, under "Category" choose "Custom" and enter "d-mmm-yy" and click OK - all dates now formatted as desired. Example attached..

Date Issue Solved.xlsx
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel
0 dipaki over 7 years ago

Thanks Sally Manson. It was really helpful for me.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 akshayan
Certified Lead Developer
over 7 years ago

Thanks Sally, appreciate it! We wanted to avoid editing the generated excel and get the data in the right format from Appian, if possible.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sally Mason
Appian Employee
over 7 years ago

No worries! Its definitely a potential improvement for that plugin...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 judym598
Certified Senior Developer
over 7 years ago

The previous Excel plugin used to retain the cell formatting. I am having the same issue with currency data. I've formatted the column as 'Accounting' but Appian changes it to 'General' when writing to those cells and the values are not displaying correctly. The only way I've been able to adjust for this is to format the data in the SQL (or CDT value) used to write to the template which is not as nice (professional looking).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sally Mason
Appian Employee
over 7 years ago

You are always free to enhance/further develop the plugins on Forum and submit new features. As you mentioned the previous plugin (though no longer recommended for use) maintained the formatting, it could be something you/someone in the community could transfer into Excel Tools.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sanikat
Certified Senior Developer
over 2 years ago in reply to Sally Mason

Hi there, can someone mention the expected date format that Appian can accept as a valid date while inserting in database table ?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Peter Lewis
Appian Employee
over 2 years ago in reply to sanikat

Keep in mind that Appian has a date type, so as long as you use that type you should be able to insert the data fine using a write to data store entity. If I misunderstood, please provide a lot more information about what you're trying to do (e.g. how are you inserting the data, what error messages have you seen, etc.).

Also I'd strongly suggest creating a new post - this thread is 5 years old and is not related to your question.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel