1. Tempo Avatar Tempo
  2. Prime
  3. servertimesheet
  4. Issues

Excel export date format is wrong

Issue #759 resolved
Ronald Vallenduuk created an issue

I've just run a time report on the first week of February. The excel export gives me a report for January 2nd, February 2nd, March 2nd... If it was just using US date format it would be ugly but workable but Excel interprets the dates as European date format so I can't do any calculations on the dates.

Comments (25)

  1. Andriy Zhdanov

    Hi Ronald,

    Add-on uses browser locale settings to format date. In my case, it does produce dates like 02/01/2016 2:16:09 PM, for the 1st of February. Could you please look inside the downloaded file in some text editor and see what is the format for you?

    May be you can adjust your computer settings and specify proper regional configuration?

    Thank you.

  2. Ronald Vallenduuk reporter

    I'm sorry but that's just total BS. My region/language settings are correct. Time log entry in old timesheet report: 01/02/2016 08:11 Same entry in the new timesheet report: 02/01/2016 08:11 And no, I didn't change my browser or region settings between downloading the two reports.

  3. Andriy Zhdanov

    Do you mean that Excel shows it correctly now? If not, how does date look like in text editor?

  4. Andriy Zhdanov

    Could you please check if it's better now? I've changed format to February 3, 2016 12:00 AM. You may need to reload add-on page though.

  5. Ronald Vallenduuk reporter

    I can see the new date format but the date cells are now a string, not a date. That means Excel date functions like MONTH(B2) no longer work. When I compared old and new timesheet I meant the old time sheet report from the free plugin.

  7. Ronald Vallenduuk reporter

    No, it should be changed to 01/02/2016. Why can't you export dates in the same format as the old plugin?

  8. Andriy Zhdanov

    Ah, I see. Sorry did not notice the different order. This is because old plugin uses server side locale settings, apparently it is set for en_UK, but new add-on uses client side settings, so looks like your browser says it's en_US for some reason.

    So format is basically the same, but it depends on locale settings. I will try if I can get user locale from JIRA in add-on, but may be it's more correct to fix your locale settings in system or browser.

    Hope it makes sense.

  9. Andriy Zhdanov

    Sorry, I seem have found the problem, formatting script does not have support for other locales than en_US. Should be fixed soon.

  14. Ronald Vallenduuk reporter
    • changed status to open

    Date format is still wrong. First week of February and my dates look like 02/04/2016 09:41. If I do MONTH(date cell) in Excel I get 4, not 2.

  15. Former user Account Deleted

    Hi Andriy I am seeing the same thing. In html, the entry looks like this: Project Type Key Title Billing Project Created Started Username Display Name Timespent (h) Comment CORETEX Task CTX-3 RUC HO Discovery & SOP Non-billable 01/20/2016 11:04 AM 01/12/2016 11:04 AM Uzair Uzair Ahmed 6
    CORETEX Task CTX-3 RUC HO Discovery & SOP Non-billable 01/20/2016 11:05 AM 01/13/2016 11:05 AM Uzair Uzair Ahmed 4
    So date formats are US, but should be European.

    What is more, Excel cannot interpret these as it is expecting European date format. So 01/12/2016 is treated as a valid date (1 Dec 2016), but it is wrong - it should be 12 Jan 2016. Then, 1/13/2016 is not valid so is converted to text. The result is a mixture of text showing US date format, and dates which are incorrect because the month and day are reversed.

    Project Type Key Title Billing Project Created Started Username Display Name Timespent (h) CORETEX Task CTX-3 RUC HO Discovery & SOP Non-billable 01/20/2016 11:04 AM 1/12/16 11:04 Uzair Uzair Ahmed 6 CORETEX Task CTX-3 RUC HO Discovery & SOP Non-billable 01/20/2016 11:05 AM 01/13/2016 11:05 AM Uzair Uzair Ahmed 4

    Regards, Robin Watts

  16. Andriy Zhdanov

    Hi Ronald, Robin,

    I'm sorry for this, it appears my testing was incorrectly successful last time, so I've tried to fix it again. Finally, it appears that it's not possible to get browser locale, so I've changed add-on to use jira user locale settings. Please let me know if works better.

    Thank you.

  17. Former user Account Deleted

    Hi Andriy, unfortunately the date issue is not resolved for me. My JIRA locale is UTC+13 (New Zealand Daylight Savings Time).

    Here is the comparison of the same records in the old and new reports - both have just been run. OLD REPORT - dates have been set correctly to February (Report was run for 1/Feb/16 to 19/Feb/16) [image: Inline images 2]

    NEW REPORT - dates are still reversed MM<->DD. Dates where the day is > 12 are not converted to dates but are left as text. [image: Inline images 3] Dates which are converted are now incorrect - I have highlighted a cell where I changed the format to show the Month Name. [image: Inline images 4]

    This is the html view created by the report. [image: Inline images 5]

    On the positive side, the totals from the new report agree with the old report. However the date format issue prevents calculation of month and week numbers as Ronald has pointed out.

    Cheers, Robin

  18. Andriy Zhdanov

    Hi Robin,

    Sorry, I'm not sure how your regional settings are supposed to be set in JIRA Cloud, but this is more than just Time Zone. JIRA sends user locale to add-on, but it's wrong probably. Could you please check what do you have in Administration - System - System Info for the User Locale property. Is it English (United States)?

    Thank you.

  19. Former user Account Deleted

    Hi Andriy, yes it is English (United States)- we can't find a way to change that.

    How did the old report work out the dates? It got them correct (mostly - although see the note below)

    I did notice that the old report changed the logging dates from our Polish dev team by 12 hours, so for example if they posted time at 2:30am NZT, it would get converted to 2:30pm on the previous day. Here's an example: JIRA Time log: [image: Inline images 1]

    OLD Timesheet Report 15/12/15 14:33 Piotr Zajac 2.5

    NEW Time Report (date-time is left as text because the MM/DD format is reversed and 16 is not a valid month). 12/16/2015 2:33 AM piotrz@sublimegroup.net Piotr Zajac 2.5

    Thanks, Robin

  20. Andriy Zhdanov

    Hi Robin,

    Thank you for checking, so the problem is clear, but I'm still looking for an appropriate solution.

    I think that old report was formatting it properly because it uses OutlookDateFormat defined in JIRA Look and Feel settings. New add-on does not have this information from JIRA. I've asked Atlassian to add means for this ACJIRA-839 and may be even meet my expectations that browser locale settings are passed to add-on ACJIRA-840

    At the same time may be you could try to ask Atlassian Support to change User Locale for you according to the Setting Locale In JIRA KB article?

    As for 12 hours difference, I believe it is expected behaviour due to support for Multiple Time Zones.

    Thank you.

  22. Andriy Zhdanov

    Hi Ronald,

    Thank you for your help. I appreciate it! I've been trying so hard to match user locale settings, that did not even think there can be some universal format for Excel. I've applied the change. Hope it will work for all!

  24. Ignacio Pulgar Martín

    @azhdanov , what's the universal format for Excel?

    I will update the issues on Atlassian's JIRA instance.

  25. Andriy Zhdanov

    Hi Ignacio,

    I think it should be like Ronald wrote iso-like yyyy-MM-dd HH:mm:ss rather than US/UK style.

    Thank you.

  26. Log in to comment
Assignee
–
Type
bug
Priority
blocker
Status
resolved
Component
Time Reports
Version
2.9
Votes
0
Watchers
1