Date format

[Dhilip_Kumar]

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.

[ShankerV · Accepted solution]

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

 

Many thanks

Shanker V

[DataNath]

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]

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