3.50 FAQ-283 How do I convert date data from Excel to Origin

Last Update: 8/25/2017

Origin uses a 12 hour offset from the Astronomical Julian Day Number as 0 (January 1, 4713 BCE 12:00 AM). No assumptions are made about time zones or any time shifting scheme (such as Daylight Savings). For example, 11 June 1998 at 21:23:01 has an Origin Julian Day Number of 2450975.890984.

Excel supports two date serial number systems: the 1900 and 1904 date systems. Microsoft Excel for Windows uses the 1900 date system by default, which defines January 1, 1900 as date serial number 1. Microsoft Excel for Macintosh uses the 1904 date system, which defines January 2, 1904 as date serial number 1.

The following tables show how to bring different formats of Date data into Origin.

Date Format in Excel (1900 date system) On or Before 2/28/1900 On or after 3/1/1900
Numeric Add 2415019 Add 2415018
Text No Conversion Needed No Conversion Needed
Date Add 1 Day No Conversion Needed


Date Format in Excel (1904 date system) On or Before 3/1/1904 On or after 3/2/1904
Numeric Add 2416480 Add 2416480
Text No Conversion Needed No Conversion Needed
Date Add 4 Years and 2 Days Add 4 Years and 1 Day


Notes:
  • There are two conversions (before or after 2/28/1900) because Excel counts 1900 as a leap year, when in fact it was not.
  • Users should understand that dates imported from Excel can look like dates in Origin, yet still be interpreted by Origin as Text & Numeric data. In order for Origin to treat date and time data properly, you must set the Format and Display options in the column's Properties dialog box.

If you copy the Date data as Date format from Excel in 1904 system, and paste it into Origin, you will find that it is wrong. In order to make it correct, you have the following two options:

1. Correcting the Date data in Origin

In this scenario, you can add the years and days shown in the second table manually, or add 1462 to the Date data. If you want to correct the Date data automatically by adding 1462, you can:

Note: If you have been set the format of the Date column to Date format in Origin before pasting from Excel, you can ignore both Step5 and Step6.
  1. Select the column where the Date data is, and then right click to select Set Column Values... in the context menu.
  2. In Set Values dialog, enter the script like below, where C is the short name of the column.
    How do I convert date data from Excel to Origin.png
  3. Click OK button, and then the Date data will be added 1462 and also turned into Text & Numeric format.
  4. Select the column where the Date data is again, and then right click to select Properties... in the context menu.
  5. In Column Properties dialog, go and select Date in the drop-down list of Format in Option item.
  6. Click OK button, and you will see the correct Date data.
2. Changing the date system in Excel

In this scenario, you can go to File: Option from Excel menu to open Excel Options dialog, and then select Advanced in the left panel and scroll down to When calculating this workbook. In this item, you can change the data system by unchecking use 1904 date system.

Keywords:Julian Day Number, numerical date system, date serial number system, Excel