Exporting custom formats with current layout

[micpete]

Hello,

Whenever I export data with the current layout to excel, any custom format for example I have a custom format for time 00:00:00 - on the excel exported it does not include my custom format, instead the format shows as 000000.

Is there a way to keep my custom format when exporting with current layout, if I were to export with summarized layout my custom format is kept.

[micpete]

Hello,

This is what I get when exporting the file with current layout to xlsx:

2024

Filter 180

1924450

2123937

1973043

1925540

I am using the custom format option in power Bi to change the way time looks to the below format, I have tried changing the custom format to the way you have formatted it and it's still giving me the same result.

When I export the data I do also receive this error when opening the file:

[v-zhengdxu-msft]

Hi @micpete 

I've done a lot of testing, but I've never had a problem like yours. If possible, please share your PBIX file for further testing. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

At the same time, you can also go to the Excel forum to post and see if there is a problem with Excel.

Excel | Microsoft Community Hub

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhengdxu-msft]

Hi @micpete 

In my test, this feature works well:

In Excel:

And here are some steps for your reference:

Make sure your time data in Power BI is correctly recognized as `Date/Time`. Sometimes, this alone can solve formatting issues.

If all else fails, you can apply the custom format manually in Excel after exporting. Select the time column, right-click, choose **Format Cells**, and set the custom format to 'HH:mm:ss'.

Just as rajendraongole1 posted that you can create a new calculated column in Power BI that formats your time as text. This can help in preserving the format when exported.

FormattedTime = FORMAT([YourTimeColumn], "HH:mm:ss")

Hope these steps can help mitigate the issue.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @micpete - This issue arises because Power BI exports are limited in how they transfer formatting to Excel, particularly with custom formats in "Current Layout."

You mentioned that exporting in "Summarized Layout" preserves the format. If your data needs and formatting requirements allow, this may be the simplest solution, as Power BI better preserves custom formatting in this layout.

Although not ideal, you could create an Excel macro or use conditional formatting rules to reapply the custom format upon opening the export file. If this is a repeated task, automating it within Excel might save time.

TimeFormatted = FORMAT('Table'[TimeColumn], "HH:MM:SS")

This way, the custom format is saved as text, which might carry over better during export. However, this method will treat the values as text, not numbers or time, in Excel.

Ideas - Microsoft Fabric Community

if it not works, raise it as an idea by using the above link

[micpete]

Hi all,

I should have mentioned that I'm using a measure for calulating total time, I've had to convert time to seconds then a calcuation to sum each hour, minute, second total, so I don't have the option to change the data type to time as the value presented looks like a time value, example return output could be 2160800 which I've then used the custom format option in power bi to ":" to separate the value into hours, mins, seconds so that it looks like 216:08:00. I've tried using TimeFormatted = FORMAT('Table'[TimeColumn], "HH:MM:SS") as you mentioned in your post but I get an error message.

[v-zhengdxu-msft]

Hi @micpete 

I did the same thing as you, but it works well too.

If possible, could you please share some raw data(exclude sensitive data) or create some sample data. It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.