Formatting date text string to a custom format

[Amazing_Random]

For a report I have to use a link into the meter name. I have been able to crack down the id aspect of the link. but the date on such link is quite off to the date/time formats in PBI.

&chartEndDate=2023-01-10T19%3A00%3A00.000Z 

This is how the date is formatted in the link

And this is how the date time format is in the column I have:

Relief date (same as chartEndDate in link)
10/01/2023 5:30:00pm.

For the link to work I'd need to have the column be in the same way as the link so this should be in the column instead of the above:

2023-01-10T19%3A00%3A00.000Z 

TIA

[JB505]

If you can't use find, here is a variation going off of what ryan_mayu posted. Could probably be a little cleaner. Wasn't sure if midnight would be 00 or 24, but should be an easy change if needed.

Column 2 =

VAR _day = LEFT('Table'[column], 2)

VAR _month = MID('Table'[column], 4, 2)

VAR _year = MID('Table'[column], 7, 4)

VAR _hourstart = IF(MID('Table'[column], 13, 1) = ":", 13, 14)

VAR _hour = IF(_hourstart = 13, MID('Table'[column], 12, 1), MID('Table'[column], 12, 2))

VAR _minute = LEFT(RIGHT('Table'[column], 7), 2)

VAR _second = LEFT(RIGHT('Table'[column], 4), 2)

VAR _am_pm = IF(RIGHT('Table'[column], 2) = "pm", "pm", "am")

VAR _hourconversion =

IF(_am_pm = "am",

    SWITCH(

        TRUE(),

        _hour IN {"1", "2", "3", "4", "5", "6", "7", "8", "9"}, "0" & _hour,

        _hour = "12", "00",

        _hour

    ),

    SWITCH(

        TRUE(),

        _hour IN {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11"}, FORMAT(FORMAT(_hour, 0) + 12, ""),

        _hour

    )

)

RETURN

_year & "-" & _month & "-" & _day & "T" & _hourconversion & "%3A" & _minute & "%3A" & _second & ".000Z"

[Amazing_Random]

Hi! This one is much closer but I'm experiencing issues with some rows. It seems it doesn't work for all the dates I have

[JB505]

@Amazing_Random 
Can you include both columns with the original date and the new formatted date?

[bolfri · Accepted solution]

Do you need something like this? This formula uses the UTCNOW() function to get the current UTC time and the FORMAT function to format the datetime in the desired format. You can replace UTCNOW() function with the column you have.

Column = CONCATENATE(
    FORMAT(UTCNOW(), "yyyy-MM-ddT"),
    SUBSTITUTE(
            FORMAT(UTCNOW(), "HH:mm:ss.fffZ"),
            ":",
            "%3A"
    )
) 

[Amazing_Random]

Yours was very close, I had managed to convert the date into the yyyy-mm-ddTHH:mm:ss format so I just needed ti use:

SUBSTITUTE(
            FORMAT(Column, "YYYY-mm-ddTHH:mm:ss.fffZ"),
            ":",
            "%3A"

Thanks for the help!

[ryan_mayu]

does that mean you want to convert "2023-01-10T19%3A00%3A00.000Z " to "10/01/2023 5:30:00pm"?

[Amazing_Random]

The opposite! I need to convert "10/01/2023 5:30:00pm" to "2023-01-10T19%3A00%3A00.000Z " for the link to work

Edit: I understand the hours are different, just an example

[ryan_mayu]

could you pls explain the converting logic? 

the first part before T is easier to understand. How about the part after letter T?

[Amazing_Random]

Sure! So after T should be the hour, then minutes, then seconds, as for html, apparently time is written like that, with %3A being the substitute for ":" I hope this helps. 

[ryan_mayu]

is 000Z a fixed ending?

[Amazing_Random]

Yes it is the Timezone I believe

 Ok I formatted the date to:

2023-01-10T17:30:00

However, the timezones are different, the URL is using England's Time Zone (GMT) and I'm in (GMT+13), I also have to add the '.000Z' at the end, which should be the GMT timezone code in url 

[ryan_mayu]

@Amazing_Random 

pls check if this is what you want

Column 2 = 
VAR _y=mid('Table'[column],7,4)
VAR _m=mid('Table'[column],4,2)
VAR _d=left('Table'[column],2)
VAR _a=find(":",'Table'[column],1)
VAR _h=if(right('Table'[column],2)="pm", mid('Table'[column],12,_a-12)+12,"0"&mid('Table'[column],12,_a-12))
VAR _min=left(right('Table'[column],7),2)
VAR _s=left(right('Table'[column],4),2)
return _y&"-"&_m&"-"&_d&"T"&_h&"%3A"&_min&"%3A"&_s&".000Z"

[Amazing_Random]

It says 

"The search Text provided to function 'FIND' could not be found in the given text"

[ryan_mayu]

could you pls provide some sample data? 

[Amazing_Random]

I'm afraid I cannot because it is some sensitive data from my company. 

[Ahmedx]

Column = 
VAR _t1 =
    LEFT ( [datetime], SEARCH ( "T", [datetime], 1 ) - 1 )
VAR _t2 =
    SUBSTITUTE ( _t1, "-", "|" )
RETURN
    DATE ( PATHITEM ( _t2, 1, INTEGER ), 
           PATHITEM ( _t2, 3, INTEGER ), 
           PATHITEM ( _t2, 2 , INTEGER )
           )

[Amazing_Random]

I think you did it the wrong way,it is supposed to be the opposite