Forum Discussion
Changing date to display correctly
I have the Created column from a Sharepoint list displayed as follows:
How do I get this to diplay in the right time, which would be subtracting either the -4 or the -5 hours as shown. I cannot get this column to format so that it is the local time when the record was created, even though it is right there on the screen.
This has to be easy, surely. Thanks!
Problem: There is a field that comes across from the data source as the UTC date value, which will have a -4 or -5 attached to it, but no way to adjust to display with the actual time it was entered (in local EST time)
Fix:
- Change your datetime field to Date/Time/TimeZone. In my example the field is [Created]
- Get the hours different from UTC your date time is by adding a new column with this formula:
= DateTimeZone.ToUtc([Created]))
Note: I called this field “UTCDifference”
3. Create a new column to adjust the Created field by the UTC using the following formula to give you the time that was actually entered into SharePoint:
= [Created]-#duration(0,[UTCDifference]*-1,0,0))
Note: I multiply by -1 instead of using an ABS function
Note: I called this field “LocalDateTime”
4. Change the type of the “LocalDateTime” column to be DateTime
Note: This last step removes the UTC part of the date timestamp.
Transform Results:
This is what it looks like in Transform. The highlighted steps are the ones corresponding to the ones above. The other steps were just to rename and remove some of the columns and aren’t necessary for the solution.
Tip: If this column shows up in the following format when you add it to your Power BI then see below as to how to fix it so it displays as a Date Time field.
You can toggle between having it display in the above hierarchical format and the datetime format by selecting one of the two options under columns. A screen shot follows here:
End Result:
This is what it looks like in Power BI:
5 Replies
Right time for who? Are your report viewers all in the same timezone?
Yes, they are. I just need the time to take off the -4 or -5 hours indicated in the field and it will be correct. If you subtract these from the time shown it is accruate as to what is in the SharePoint data source.
Problem: There is a field that comes across from the data source as the UTC date value, which will have a -4 or -5 attached to it, but no way to adjust to display with the actual time it was entered (in local EST time)
Fix:
- Change your datetime field to Date/Time/TimeZone. In my example the field is [Created]
- Get the hours different from UTC your date time is by adding a new column with this formula:
= DateTimeZone.ToUtc([Created]))
Note: I called this field “UTCDifference”
3. Create a new column to adjust the Created field by the UTC using the following formula to give you the time that was actually entered into SharePoint:
= [Created]-#duration(0,[UTCDifference]*-1,0,0))
Note: I multiply by -1 instead of using an ABS function
Note: I called this field “LocalDateTime”
4. Change the type of the “LocalDateTime” column to be DateTime
Note: This last step removes the UTC part of the date timestamp.
Transform Results:
This is what it looks like in Transform. The highlighted steps are the ones corresponding to the ones above. The other steps were just to rename and remove some of the columns and aren’t necessary for the solution.
Tip: If this column shows up in the following format when you add it to your Power BI then see below as to how to fix it so it displays as a Date Time field.
You can toggle between having it display in the above hierarchical format and the datetime format by selecting one of the two options under columns. A screen shot follows here:
End Result:
This is what it looks like in Power BI:
