Date format changes after exporting to Excel from Jira Data Center
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
The date format shown in Excel does not reflect the format shown in the Jira application's user interface.
Cause
- 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.
- There is also a known bug regarding the date format change even though the setting in Javascript is configured correctly (JRASERVER-67469 - Inconsistency date Export using Date Custom Field with different Language Configured).
Diagnosis
Ensure the end user machine's Regional and Language setting is not other than English.
Resolution
- Change the date format on your local machine to the one used in Jira applications.
- Use Google Excel Sheet (Ensure you are using English Language else the issue will persist)
- Open Google Excel
- Select File > Import > Upload > Upload the CSV file
- Once the import is completed, a screen will prompt for more details. Update the Separator type accordingly.
- Click on import data and open it
- The wrong date data will be imported to Google Excel, we will need to select 2 columns which having the wrong date format.
- Select Format > Number > Date Time and all date columns will be formatted accordingly.
- Export it as a new Excel file and you can use it as usual in Microsoft Excel without issue now.
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.
The date/time format reverts to absolute after a week.
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
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.
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.
- For Java formats, specify date/time formats based on the Java SimpleDateFormat.
- For JavaScript formats, specify date/time formats based on the Unix date format.
Here are some example US-based date configurations:
Preferred Date | Value of the | Value of the | Comments |
|---|---|---|---|
| 2010-10-01 | yyyy-MM-dd | %Y-%m-%d | ISO 8601 format |
Oct/1/10 |
|
| |
10/01/10 |
|
| |
Oct 1, 2010 |
|
| |
10/01/2010 |
|
|
Here are some examples of date/time configurations:
Preferred Date/Time | Value of the | Value of the | Comments |
|---|---|---|---|
| 2010-10-15 08:50 | yyyy-MM-dd HH:mm | %Y-%m-%d %H:%M | ISO 8601 format |
15/Oct/10 8:50 AM |
|
| |
10/15/10 08:50 AM |
|
|