Date format changes after exporting to Excel

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The date format shown in Excel does not reflect the date format which is shown in the JIRA application user interface.

Cause

  1. Excel determines the date format based on local settings. If your local machine has a different date format than JIRA applications, then the settings will not match each other.
  2. There is also a known bug regarding the date format change even though the setting in javascript is configured correctly ( JRASERVER-67469 - Getting issue details... STATUS ). 

Diagnosis

Ensure the end user machine Regional and Language setting is not other than English.

Resolution

  1. Change the date format on your local machine to the one used in JIRA applications.
  2. Use Google Excel Sheet (Ensure you are using English Language else issue will persist)
    1. Open Google Excel
    2. Select File > Import > Upload > Upload the CSV file
    3. Once import completed, it will prompt up a screen asking for more details, update the Separator type accordingly.
    4. Click on import data and open it
    5. The wrong date data will be imported to Google Excel, we will need to select 2 columns which having wrong date format.
    6. Select Format > Number > Date Time and all date column will be formatted accordingly.
    7. Export it as a new Excel file and you can use it as usual in Microsoft Excel without issue now.

The Look and Feel page allows you to customize the way times and dates are presented to users throughout the JIRA user interface.

When specifying dates and times, they should be based on the Java SimpleDateFormat.

When you are not in edit mode on the 'Look and Feel' page, the examples in the rightmost column of the Date/Time Formats section show you how the various formats will appear in JIRA.

Relative time is used in date/time formats

Issue date/time fields show a relative instead of absolute date/time format (for example: "Yesterday" instead of "20 May 2013 12:00 PM"). You can still see the absolute date/time by hovering over the field.

(info) The date/time format reverts to absolute after a week.

If you want to switch off this format, set the jira.lf.date.relativize application property to 'false'. See Advanced JIRA Configuration and Display standard dates in Jira server for more information.

Configuring date picker formats

JIRA system administrators can configure the format of date pickers used throughout the JIRA user interface via options on the Advanced Settings page.


(info) Be aware that these options are different from the Date/Time Formats configuration options on the Look and Feel page, which only customize JIRA's presentation of times and dates to users.

(warning) The date or date/time formats for date pickers are defined by a pair of properties (one for Java and the other for JavaScript). The two properties in this Java/JavaScript pair must match in order for the date (or date/time) picker they define to function correctly.

Here are some example US-based date configurations:

Preferred Date

Value of the jira.date.picker.java.format property

Value of the jira.date.picker.javascript.format property

Comments
2010-10-01yyyy-MM-dd%Y-%m-%dISO 8601 format

Oct/1/10

MMM/d/yy

%b/%e/%y


10/01/10

MM/dd/yy

%m/%d/%y


Oct 1, 2010

MMM d, yyyy

%b %e, %Y


10/01/2010

MM/dd/yyyy

%m/%d/%Y


Here are some examples of date/time configurations:

Preferred Date/Time

Value of the jira.date.time.picker.java.format property

Value of the jira.date.time.picker.javascript.format property

Comments
2010-10-15 08:50yyyy-MM-dd HH:mm%Y-%m-%d %H:%MISO 8601 format

15/Oct/10 8:50 AM

dd/MMM/yy h:mm a

%d/%b/%y %l:%M %p


10/15/10 08:50 AM

MM/dd/yy hh:mm a

%m/%d/%y %I:%M %p


Last modified on Jun 15, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.