Alteryx Designer Desktop Discussions

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

Date format

Dhilip_Kumar
8 - Asteroid

We received dates format as "mm/dd/yyyy or yyyy/dd/mm" so I need to change date format to mm/dd/yyyy if we received both format. I used if formula but its not working.

 

If DateTimeParse([Date],'%Y/%m/%d') then Datetimeformat(DateTimeParse([Date],'%Y/%m/%d'),"%m/%d/%Y") else [Date] endif

 

Please do the needful.

3 REPLIES 3
ShankerV
17 - Castor

Hi @Dhilip_Kumar 

 

Please use the below formula.

 

IF REGEX_Match(Left([Field1],4),"\d{4}")
THEN datetimeformat(DateTimeParse([Field1],'%y/%d/%m'),"%m/%d/%Y")
ELSE [Field1]
ENDIF

 

Screenshot 2023-08-18 155456.jpg

 

Many thanks

Shanker V

DataNath
17 - Castor
17 - Castor

Hey @Dhilip_Kumar, you can adjust your current formula very slightly by wrapping the initial if check in an !isnull() - and swapping the %d and %m you had the wrong way round to get the outcome you're after. For the yyyy/dd/mm records, they won't be null and so it'll do the conversion. For records already in the desired format, they'll be left as is! That looks like this:

 

If !IsNull(DateTimeParse([Date],'%Y/%d/%m')) then Datetimeformat(DateTimeParse([Date],'%Y/%d/%m'),"%m/%d/%Y") else [Date] endif

 

Dhilip_Kumar
8 - Asteroid

Thanks you so much. It's works!!!!!! Much Appreciated

 

Labels
Top Solution Authors