Losing Date Format when using an IF statement

[drew_mcspy]

Hi

I have a date format in the data load editor:

date("Date",'MMM-YY') as Date which returns the date as Aug-19 for example.

The formula below returns 'Active' if the max date is equal to the current date, otherwise it returns 'Cancelled'.

if(Count({$< Date = {"$(=Max(Date))"} >} Date)>0,'Active','Cancelled')

What I want is for the 'Cancelled' to be replaced by the max date and therefore I replaced 'Cancelled' with max(Date) however instead of returning Mar-19 it will show as 43525.

if(Count({$< Date = {"$(=Max(Date))"} >} Date)>0,'Active',max(Date))

Is there a way the number 43525 can instead display as the correct format of 'MMM'YY'?

Andrew M

[trdandamudi]

Give a try with below:

if(Count({$< Date = {"$(=Max(Date))"} >} Date)>0,'Active',Date(max(Date),'MMM-YY'))