Forum Discussion
Time format [h:mm tt]
I have a text field which contains value in the form of:
10:30:00
14:00:00
When I tell that this field contains time value and apply the time format [h:mm tt] the result is
10:30 tt
14:00 tt
I expected the result as
10:30 AM
2:00 PM
How can we assign the correct time format for such values.
12 Replies
As of today, this still seems to be an issue...
I found another SQL blog that pointed me to using AM/PM instead of the t or tt format it it works. My format command is this
FORMAT(DateTable[Date]),"M/d h:mm AM/PM")This particular format shows "9/29 11:32 AM"
Works great
rgines, formatting on import can be a good way to go.
The tt option within the data model does work now though.
Good to know. I'm working within the desktop tool within a Measure. It doesn't work there with the tt option. Have to use AM/PM. Might work with A/P but I didn't try that.
Came across this thread while searching for a solution from a measure using FORMAT() and "tt" returing a 24 hour result, instead of a 12 hour result.
Im my situation, query pulls in date/time as a 12 hour format. When using a measure to return a date value, I can correctly format it to "m/dd/yy h:mm tt". I was using this to create a text value; this is where the formatting broke.
Original Formula = "Last Refresh: " & FORMAT( [DateTime] , "m/dd/yy h:mm tt")
Result = Last Refresh: 2/22/17 13:18
Correct Formula = "Last Refresh: " & FORMAT( [DateTime] , "m/dd/yy h:mm AM/PM")
Result = Last Refresh 2/2/17 1:18 PM
Sent a frown for the documented "tt" formatting not working, but using AM/PM, am/pm, A/P, a/p works without issue.
emteka - Can you clarify? If you are setting the Time value in the service, how are you doing that?
Or, is this in Desktop that you are setting the Time value and if that is the case, does it show up correctly in Desktop but not in the Service?
I believe he is saying that when you load a Date/Time into Power BI Desktop and select a date/time format with 'TT', it doesn't return AM/PM.
It just returns 'tt' instead of AM/PM.
I have recreated the problem by importing an Excel spreadsheet with a number of date/times into Power BI Desktop. I haven't figured out how to fix it as yet.
For the life of me, I cannot recreate this. Are you on the January update and are there any regional language settings involved?
I'm using the latest Power BI desktop. I did some testing with simple Excel where I put in one column time values. Regardless of Excel format (forced text format or time format) values shown in Power BI are like
10:00:00 tt
7:00:00 tt
they simply doesn't show the AM/PM as the suffix.
EIGHT YEATS LATER...
and this STILL A BUG that Microsoft won;t fix.
Thanks for the help figuring out Microsofts' errors.
FU, Microsoft. It's **bleep** like this why I hate you.
