How to Convert Julian Dates to Regular Dates in Excel

i Jupiterimages/liquidlibrary/Getty Images

The astrological Julian calendar, created by Julius Ceaser in 46 BC, tracked dates as a cumulative number of passing days since Jan 1, 4713 BC. This format is often used in business databases, but more frequently businesses use a 5-digit number loosely based on this Julian system. Although technically inaccurate, these dates are also considered Julian dates in business and technology circles. This shortened format uses the first 2 digits to express the year with the last 3 digits expressing the number of days since the beginning of the year. Microsoft Excel 2010 does not offer a conversion utility, but you can use a formula to convert the dates.

Five-Digit Julian Dates

Open your spreadsheet in Microsoft Excel 2010 and locate the column containing the Julian dates. These cells should use a 5-digit format similar to 12001 for Jan. 1, 2012. If the dates use only 4 digits, Excel might be cutting off a leading zero.

Select the cells containing the Julian dates, right-click the selection and choose "Format Cells." Choose "Text" from the Number tab and click "OK." Manually enter a zero before any 4-digit Julian dates.

Enter the following in the next empty column on the same row as the first Julian date:

\=DATE(IF(0+(LEFT(Julian_Date,2))<30,2000,1900)+LEFT(Julian_Date,2),1,RIGHT(Julian_Date,3))

Replace "Julian_Date" with the cell reference containing the date. As an example, if you had a Julian date in cell C1, enter the following in cell D1:

\=DATE(IF(0+(LEFT(C1,2))<30,2000,1900)+LEFT(C1,2),1,RIGHT(C1,3))

Click the black square at the bottom of the cell containing the formula and drag it down the column to copy the formula to all highlighted cells. In the example, you might click the bottom right corner of cell D1 and drag down to cell D20 to convert the Julian dates in cells C1 through C20. The results appear in cells D1 through D20.

Astrological Julian Dates

Open your spreadsheet in Microsoft Excel 2010 and locate the column containing the astrological Julian dates. Modern dates will have 7 digits before the decimal point.

Enter "=Julian_Dates-2415018.5" (without quotes here and throughout) in the next empty column and change "Julian_Dates" to reference the first cell containing a Julian date. Click and drag the bottom right corner of the cell containing the formula to copy the formula down the column.

Select the converted dates, right-click the selection and click "Format Cells." Click "Date" from the Number tab and click "OK" to format the results using the Gregorian calendar system.