Date conversion

[Sarath27]

Hi All,

Please help me to convert the date to the required format (given in Balance Date Out)

Balance Date                         Balance Date Out                

2024-12-06                             20240612
06/13/2024 00:00:00              20240613

[Bobbyt23 · Accepted solution]

You could use this formula to get the result:

IF Substring([Balance Date],2,1)='/'
THEN Substring([Balance Date],6,4)+Substring([Balance Date],0,2)+Substring([Balance Date],3,2)

ELSE Substring([Balance Date],0,4)+Substring([Balance Date],5,2)+Substring([Balance Date],8,2)
ENDIF

[binu_acs · Accepted solution]

@Sarath27 one way of doing this

[Sarath27]

Hi @binu_acs 

Please see the results generated through this formula

Yellow highlighted ones are not the expected results..Date format should be YYYMMDD

[binu_acs]

@Sarath27 updated the code to read from %Y-%d-%m

DateTimeFormat(IIF(REGEX_Match([Balance Date], '\d{4}-\d{2}-\d{2}'),DateTimeParse([Balance Date],'%Y-%d-%m'),
IIF(REGEX_Match([Balance Date], '\d{2}\/\d{2}\/\d{4}.*'),DateTimeParse([Balance Date],'%m/%d/%Y'),Null())),'%Y%m%d')