Forum Discussion
Datetime column is in AM/PM format after export to Excel
Hello to the forum,
I am exporting data from a matrix in a published Power BI report to Excel. In the visualization there is a column in datetime format. This is displayed in Excel in AM/PM format, which I don't want. In the Windows system settings it is specified that the time should not be displayed in AM/PM format.
By the way, I use Windows, Power BI Desktop and also the browser in German.
Below I will describe in detail how I proceed:
In the matrix you can see the Date and time column.
The data there is in the format "DD.MM.YYYY hh:mm:ss".
The date type of the column is Date/Time.
To export the data, I select the Export data command.
My matrix with "Date and time"-column
I select the Data in current layout option and click Export.
I export the data in the actual layout
In the Excel file, the Date and time column has the AM/PM format.
The table with the exported data
In the Windows 11 control panel you can see how the time should be displayed.
Regional settings in the Windows control panel
In the other settings you can see that the AM and PM symbols are not used.
Settings for AM and PM
The Power BI documentation says the following about this:
When you export datetime data from Power BI, the format of the datetime changes to match the datetime format of your local machine.
Microsoft Learn: Export the data that was used to create a visualization
Text from the Power BI documentation
Can you tell me why the column is still displayed in AM/PM format?
Thank you and best regards!
4 Replies
The other element to consider here is the date/time format of the column or measure itself. Find the column/measure within Power BI Desktop and check what formatting is being applied in the model itself. This is different to the localisation you have mentioned. This formatting will be what the date/time looks like. For example, do you want month as a word or 2 numbers. How many numbers for year. Do you want AM/PM etc. In other places this is known as the format string.
The localisation you mentioned gets applied later. Certain format strings will change with localisation. For example AM/PM might show diffferently in another language. A format like DD/MM/YYYY might change to MM/DD/YYYY in America.
Hello Anonymous ,
thanks for your answer!
In Power BI Desktop, the column also has a date/time format without AM/PM. The data type is also
date/time. So that can't be the problem.
The column in Power BI Desktop
Do you have any other idea why the Excel file is in AM/PM format?
