Change settings in timesheet exports

[Klara]

Hello, 

I would like to find out, if it is possible to change settings when exporting timesheets from Jira. Currently, the exported time format is this: 2022-10-03T09:40:05+02:00. 

I would like to get a simple format, ideally if the date and time would have a unique column in the export. 

Is that possible?

Thank you, 

Klara

[Wojciech Wardaszko · Accepted solution]

Hi @Klara 

I'm with HeroCoders, the team behind Clockwork. 

About the format of date/time in the exported file, I understand you export worklogs and are referring to the "started at"column in that .xlsx file. 

That column uses the default time format - it assures that no matter where on Earth someone looks at it, they can identify exactly when that worklog was created.

We'd love to hear more about what you'd like to see there for users in different time zones. I'd greatly appreciate it if you reached out to our Support Team to discuss this in detail, although I cannot promise that we'll be able to deliver what you need.

This can take a lot of time even if we decide to do it, so in the meantime, I'd like to offer a workaround - once you have this weird date-time data in a Google Sheets column, you can make it show in whatever format you need.

If I got this right, what you need is to change that one column into two columns: one with date (respecting the timezone, so it can change in some cases), and the other with time adjusted to your timezone.

This is how I'd do it, assuming you have the "started at" in column F (like I do in my example file):

• add four new columns right to it, so you now have empty columns G, H, I and J.
• In cell G2 put the following formula: =LEFT(F2,10)
• In cell H2 put =RIGHT(F2,14)
• In cell I2 put this formula:
  
  =TIME(LEFT(H2,2),MID(H2,4,2),MID(H2,7,2))+IF(MID(H2,9,1)="+",-1*VALUE(MID(H2,11,1))/24,VALUE(MID(H2,11,1))/24)
  
  This will produce a usable time in the UTC time standard. You can append +(x/24) at the end of the above formula to adjust to your time zone, X being your difference to UTC. For example for EST x=-5 and for JST (Japanese time) x=9. You can also use the data formatting available in Google Sheets (or Excel) to display it in any format you like.
• Now let's get back to the date - it may be so that the time difference was so big that in your time zone this moment was on another day. To get the real date for your timezone, in column J put the following formula:
  
  =IF(I2<0,TO_DATE(G2)-1,IF(I2>1,TO_DATE(G2)+1,TO_DATE(G2)))
  
  
• And lastly: to get the data in all rows expand all the formulas to the bottom of the timesheet.

You can now do two more things to make this more useful. The first is to hide the unnecessary columns F, G, and H, and the second is to make this process into a macro so you can do it all with a single keystroke next time you need it. Here's a guide on how to record a macro in Google Sheets and here's a similar guide for Excel.

Hope this helps!

Cheers,
Wojciech

[Klara]

Hello Wojciech, 

thank you for your time and help, I am able to create the date and time columns now. :) I also answered your email with more details. 

Thank you again, 

Klara

[Wojciech Wardaszko]

I'm glad I was able to help :)

Thank you for your answer with those details. Would you mind sharing a little more? I sent you a little bit of a follow-up.

In any case, helping you was a pleasure  as I enjoy playing with what can be done in Google Sheets a lot :)

Cheers,
Wojciech

[Trudy Claspill · Accepted solution]

Hello @Klara 

Welcome to the community.

Exactly how are you exporting the information?

Are you using a third party time sheet application? If so, you will need to tell us which application that is. Or you would need to contact the vendor of that application for assistance.

[Klara]

We are using app called Clockwork, but you are right, I will try to contact them directly. :)