Forum Discussion
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...
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" ) )
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"
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
Amazing_Random
Can you include both columns with the original date and the new formatted date?