Changing format of the date to desired format

[AST_42]

I am using a variable to populate a field with the date which is coming from QVD.

i have written a expression in variable to get the date, but when there are multiple date , i am unable to change the format of the date.

Multiple dates are getting populated using below expression:
Date#(GetFieldSelections([RPT_DT]),'M/D/YYYY') and the format populated in excel is 

1/25/2020, 1/26/2020

 where as desired format is Jan 25, 2020, Jan 26, 2020

when i am trying to achieve this format using below expression
=Date(Date#(GetFieldSelections([RPT_DT]),'M/D/YYYY'),'MMM DD, YYYY')
i am getting  - (hyphen)

Please suggest a solution

Thanks!

[Andrei_Cusnir · Accepted solution]

Hello,

This is working as designed, because the function Date#(FIELD, 'FORMAT'), is suppose to get only one date as string and the it is trying to match it with the format 'M/D/YYYY'. Therefore, when 2+ values are selected the function is trying to match the string '1/25/2020, 1/26/2020' with the string format 'M/D/YYYY'  and it is failing so it returns (-) which is Null(). 

One workaround would be to use this within Data load editor directly:

Data:
Load 
    Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT

This will make all the values in the dataset with the format you like, so you can then simply populate them as =GetFieldSelections([RPT_DT])

OR:

If you want to keep both date formats, then in Data load editor you can simply do:

Data:
Load 
    [RPT_DT] as [RPT_DT],

    Date(Date#([RPT_DT], 'M/D/YYYY'), 'MMM D, YYYY') as RPT_DT_FORMATED

This will give you 2 fields. So you can make the selections on both fields and use only the RPT_DT_FORMATED for populating the selected values. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.