3.50 FAQ-283 How do I convert date data from Excel to OriginDate_Conversion_Origin_Excel
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.
|
- Select the column where the Date data is, and then right click to select Set Column Values... in the context menu.
- In Set Values dialog, enter the script like below, where C is the short name of the column.
- Click OK button, and then the Date data will be added 1462 and also turned into Text & Numeric format.
- Select the column where the Date data is again, and then right click to select Properties... in the context menu.
- In Column Properties dialog, go and select Date in the drop-down list of Format in Option item.
- 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
|