How to convert between Julian date and calendar date in Excel?

Generally, Julian date is a date format which includes 5 digit numbers, first two indicate the year, and last three indicate the day of the year. For example, 14001 indicates 1/1/2014 in calendar. Now this tutorial is talking about the methods on conversion between Julian date and calendar date in Excel.

Convert Julian date to calendar date

Convert calendar date to Julian date

Convert Julian date to calendar date

To convert Julian date to calendar date, you just need a formula in Excel.

1. In a blank cell, type this formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)) into it, press Enter key, if you need, you can drag the fill handle to a range with this formula. See screenshot:

Tip: A1 indicates the Julian date you need to convert to calendar date.

2. Then right click at these highlighted cells, and select Format Cells from context menu, and in the popped out Format Cells dialog, under Number tab, click Date in the Category list, then select the date type you need in the right section.

3. Click OK, then you can see all Julian dates have been converted to calendar dates.

Quickly convert nonstandard date to standard date formatting(mm/dd/yyyy)

In some times, you may received a workhseets with multiple nonstandard dates, and to convert all of them to the standard date formatting as mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert these nonstandard dates to the standard date formatting with one click.  Click for free full featured trial in 30 days!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Convert calendar date to Julian date

If you want to convert calendar date to Julian date, you can use below formulas.

In a blank cell, type this formula =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") and press Enter key, if you need you can apply this formula to a range by dragging the auto fill handle.

Tip: A1 is the calendar date you want to convert to Julian date.

Relative Articles:

• Convert between date and unix timestamp in Excel
• Convert time string to time or date and time in Excel