Forum Discussion

Amazing_Random's avatar
Amazing_Random
Helper I
3 years ago
Solved

Formatting date text string to a custom format

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. &chartEndDa...
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    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's avatar
Amazing_Random
Helper I
3 years ago

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's avatar
ryan_mayu
Super User
3 years ago

could you pls explain the converting logic? 

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

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

     

     

    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's avatar
      Amazing_Random
      Helper I
      3 years ago

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

  • Amazing_Random's avatar
    Amazing_Random
    Helper I
    3 years ago

    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's avatar
      ryan_mayu
      Super User
      3 years ago

      is 000Z a fixed ending?

      • Amazing_Random's avatar
        Amazing_Random
        Helper I
        3 years ago

        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