Can I change the time formatting settings when exporting a filter to Excel?

Question

Hey! I'm truing to export my filter with sla's time to Excel. I know that in jira, we can export a filter to an Excel spreadsheet in two ways:

1) Using the "Open in Microsoft Excel" btn

2) Using the "Export" btn

If I using the second way, i got a result what i need, more specifically, the kind of time format that I need: this  
 or like this . I had the time with hours and minutes without "h" or "m", only ":" symbol, but if use the first way, i got it 
.

The situation is such that I need to use the export only through the first path, but I do not know if I can somehow get rid of the letters "h" and "m"? In other words, using the first export method, I need to get the time in the same format as when i using the second method. Has anyone come across something similar or is there a universal solution to my problem? I assume that most likely I will have to configure something directly in Excel itself, but the trouble is that I am practically not familiar with it.

Steph Bannister · Answer

Hi! I have the same problem with option 1. Thanks for this tip on converting the h/m using find/replace. I'm not good at Macros but @Andrey Rudnev could you share some more detail about how you got the date formats to convert? I trid the simple date conversion but the initial export is recognised as text so it didn't work.

Surely though, there is a way to set the time format differently in Jira for the Excel export itself? I cant find anything for that in the SLAs themselves (seems to be no such setting for time format), and can only think to change the global time settings.

Does this relate to the global time settings in Jira? I.e. if they are set to "pretty" they come out as 4days 5 hours 6 minutes etc.

I don't want to experiment with our entire cloud site's global settings just to check this. Does anyone know if the time format needs to be changed on this level in order to fix the time format using option 1 Open in Microsoft Excel?

cheers

Steph

Andrey Rudnev · Answer

@Serhii Kibitkin_SAASJet_ Thank for your suggestion. I created some function in Excel and it it solved my problem, and in other way, i tried to create macros, how a result it works perfect.

Serhii Kibitkin_SAASJet_ · Answer

How about to do the simple replace in XLS file - Ctrl + H - replace h --> ": ", replace m --> ""

watch video how to replace h,m to : format of time - https://drive.google.com/file/d/18-E1U_u1yTaqva5XL3o8pf9LFCZ7LCZp/view?usp=sharing