Custom date field not formatting correctly compared to default date in exported excel report in ALM?

Question ID: 109515
0
0

Hello,

       I have created Custom Date field in Defects module. To export all defects, we have written a simple query in AnalysisView and exported all defects. In the exported file, date format is different(yyyy-mm-dd) for custom field where a system field DetectedOnDate is in dd/mm/yyyy format. I am trying to display custom field value in dd/mm/yyyy format. I have tried Convert method in SQL query but is not working.

Any Help is appreciated,

HomerJ

Marked as spam
Posted by (Questions: 305, Answers: 9)
Asked on April 30, 2020 12:18 pm
5 views
Answers
1
Private answer

Hello HomerJ,

 

I have experienced this before and by default Excel automatically formats the cells based on the data within and how it perceives what is filled in by default. When I viewed the formatting on the cells in the DetectedonDate it was formatted as number with a custom format category displayed as the format type m/d/yy h:mm When I viewed the cell data on the AssignedDate it was formatted as Number but text so it would do no manipulation on the displayed data even when a number is in there and displayed exactly as it came into the cell. The problem is that when the date is entered on a user defined field it stores it differently than when entered automatically by the system in the system defined field (as it pulls it from the system and stores it differently). 


Working within my lab, I was able to create a custom field, define it as type string and select the masked option (only available for string type entitities) and then create a mask of !00/99/0000 and this worked for the export of data in my testing to format it as you desired, the issue that when entering the data into that field you have to enter the date as you want it to appear (ie 01/27/2020) and the calendar to select the date doesn't appear when you enter data into that field as it must be entered manually in that form I just showed.

Unfortunately this is a limitation of the system and how it interacts with the export into excel as excel uses its default formatting. So you would either need to change the custom field type and mask it as I stated above, or after the export, you would need to manipulate the data to have the cells formatted as you desired.but this is because of the way the data is stored, exported and read by the Excel default formatting.

I hope this helps,

Dan

Marked as spam
Posted by (Questions: 0, Answers: 699)
Answered on April 30, 2020 12:35 pm