Time format [h:mm tt]

[emteka]

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.

[blodus]

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. 

[cityofglass88]

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.

[rgines]

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

[wynhopkins]

@rgines,  formatting on import can be a good way to go.   

The tt option within the data model does work now though.

[rgines]

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.

[wynhopkins]

@rgines, it should work for measures also (see screen shots)

[djnww]

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.

[Greg_Deckler]

For the life of me, I cannot recreate this. Are you on the January update and are there any regional language settings involved?

[emteka]

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.

[wynhopkins]

I've been informed by the Power BI Team that this shoul be fixed in the next update

[greggyb]

Reproduced and confirmed in PBI Desktop. I sent a frown already; you should submit this as a bug as well.

[Greg_Deckler]

@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?