How can I preserve display string (convert DOB to Age) upon excel export?

[Former Member]

I am using the following code to convert DOB to age within Display String (Format Field/Common tab):

if 
//months
(datediff('yyyy',{CDCLIENT.DOB},currentdate)-(if datepart('y',currentdate)>datepart('y',{CDCLIENT.DOB}) then 0 else 1))<2 then
totext(datediff('mm',{CDCLIENT.DOB},currentdate),0,'') & ' months'
else
//years
totext((datediff('yyyy',{CDCLIENT.DOB},currentdate)-(if datepart('y',currentdate)>datepart('y',{CDCLIENT.DOB}) then 0 else 1)),0,'') //& ' years'

When I export to excel/csv/ttx.. it doesn't export the display string (Age), but reverts back to the DOB. How do I preserve this upon export?

[abhilash_kumar · Accepted solution]

Hi Dominic,

The 'display string' formatted values are not exported to Excel Data Only.

Create a 'formula field' as a workaround.

-Abhilash

[Former Member · Accepted solution]

Thank you, but could you help with an example? going from Display string to Formula field isn't a 1-to-1. How would I re-write this?

[abhilash_kumar · Accepted solution]

You would use the same code!

-Abhilash

[Former Member · Accepted solution]

when added my formula field (which I named 'age') into the Select Expert record {@age} it said a Boolean is required.

[abhilash_kumar · Accepted solution]

Select Expert is where you'd write a formula to 'filter' records.

Go to the Field Explorer > Right-click Formula Fields > Click New > Give it a name > paste the code.

-Abhilash

[Former Member · Accepted solution]

The problem with having my current formula field code is it doesn't go anywhere or do anything. Is there additional programming to that code to activate it against the data query?

[abhilash_kumar · Accepted solution]

You need to drag and drop it on the report in place of the field that earlier had the display string formula.

-Abhilash