Want to Convert a date String to date format

[krishnasen]

Hi, I am using a third a party API to give a date time in the format September, 11, 2021, 10:55:43 or September 11 2021 10:55:43 any idea            I am already using a Dax to give a uniform format 

NewCutOff = SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(rounds[CutOff],"at ",""),"UTC",""),",",""))," ",", ")

I am not able to convert into datetime format using modelling or DATEVALUE any ideas would be helpful

[ThxAlot · Accepted solution]

Isn't it way much easier to convert the string to datetime data type by PQ?

or just a matter of clicks

[Adamboer]

To convert the date time in the format "September, 11, 2021, 10:55:43" or "September 11 2021 10:55:43" to a datetime format in Power BI, you can use the following DAX formula:

NewDateTime = IFERROR(DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ROUNDUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER([Date Time]),"am"," AM"),"pm"," PM"),",",""),"at",""),"utc",""),"-"," ")," "," "),"/","-"),"st",""),"nd",""),"rd",""),"th","")&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER([Date Time]),"am"," AM"),"pm"," PM"),",",""),"at","")),BLANK())

This formula first converts the input date time to lower case and then replaces any unnecessary text with empty strings to get a consistent format. It then uses the DATEVALUE function to convert the resulting text string to a datetime value.

Note that this formula assumes that the input date time is in the "Month, Day, Year, Hour:Minute:Second" or "Month Day Year Hour:Minute:Second" format. If the input format is different, you may need to adjust the formula accordingly.