Thursday, April 11, 2019

Exported Report/Search returns Date/Other format instead of Numbers as shown in the report

This is the normal behaviour of how MS Excel treats Numerical Data. MS Excel automatically converts numerical data to its native formats thus some numeric values may be returned as Dates or in other formats.

Alternate Solution:
We can preceed Numerical Data by single quotations (') before exporting to MS Excel so that MS Excel will take them as Text Format. Example:

Field Value: 11-03

Value in MS Excel: 3-Nov

Suggested Value: '11-03

1. To do this, we can use Fomula (Text) > Formula: Chr(39) || {Field internal ID}

2. Once the search result is exported in MS Excel or CSV, check the column of the formula field and add another column to remove the first character appearing, which is the single quotation mark.

3. Insert this formula. Replace {cell} with actual cell where the Formula Text is: =RIGHT({cell}, LEN({cell})-1)

4. This way, the first character of single quotation will be removed in your exported file, rendering the exact original numeric value of your field.  

No comments:

Post a Comment