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"
    )
)

     

     

     

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 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

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

      It says 

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

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        could you pls provide some sample data?