Let’s talk Alteryx Copilot. Join the live AMA event to connect with the Alteryx team, ask questions, and hear how others are exploring what Copilot can do. Have Copilot questions? Ask here!
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Invalid dates to Valid dates

Shaaz
9 - Comet

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.

3 REPLIES 3
PhilipMannering
16 - Nebula
16 - Nebula

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
15 - Aurora
15 - Aurora

@Shaaz,

 

Please see below how you can do it :

 

messi007_0-1640177642685.png

Attached the workflow,

 

Hope this helps,

Regards,

Shaaz
9 - Comet

Thank you for the swift response.

Labels
Top Solution Authors