Date format in if condition

[rahulramanm]

Hi,

I am trying to filter data based on date. I am using if statement here.

Code:

if(day(max(DATEFIELD))>'25','25/'&month(max(DATEFIELD))&'/'&year(max(DATEFIELD)),max(DATEFIELD))

The else part should return date in 'DD/MM/YYYY' format, but it's returning the date's floor value. I need the code to return the date in 'DD/MM/YYYY' format.

Please help.

[Lauri]

Try formatting the else part:

date(max(DATEFIELD), 'DD/MM/YYYY')

[rahulramanm]

Already tried this, not working. 😞

[Lauri · Accepted solution]

Maybe the second part of your If statement needs to be explicitly a date (with makedate) instead of text:

date(if(day(max(DATEFIELD))>'25', makedate(year(max(DATEFIELD)), month(max(DATEFIELD)), 25), max(DATEFIELD)), 'MM/DD/YYYY')

[sasiparupudi1 · Accepted solution]

may be try

if(day(max(DATEFIELD))>'25',

MakeDate(year(max(DATEFIELD)),month(max(DATEFIELD)),25),

 ,max(DATEFIELD))

[rahulramanm]

Thanks Sasi,

Just a change in code : Makedate has to be formatted to 'DD/MM/YYYY' in my case.

[rahulramanm]

Thanks Laurischarf,

Just a change in code : Makedate has to be formatted to 'DD/MM/YYYY' in my case.