Convert Invalid dates to Valid dates

[Shaaz]

Hi All,

I've a data like below.

Date

2020-01-01

2021-04-30

2021-12-31

2021-02-31

How do I get below expected output

Date

2020-01-01

2021-04-30

2021-12-31

2021-02-28

Here logic is since we don't get 31st Feb 2021, it should fall back to last day of month of that particular month (like here it is 2021-02-28) and it has to be dynamic as I might dates likes 31st Nov, 31st Sep or 45th Dec.

[PhilipMannering · Accepted solution]

Perhaps this formula works

min(datetimetrim(left(date, 7) + '-01', 'lastofmonth'), todate([date]))

But it does give you a warning in the messages, so there's bound to be a better solution.

[messi007]

@Shaaz,

Please see below how you can do it :

Attached the workflow,

Hope this helps,

Regards,

[Shaaz]

Thank you for the swift response.