How to Convert a Julian Date in Excel

Written by stephanie ellen
  • Share
  • Tweet
  • Share
  • Email

Microsoft Excel 2007 is a powerful spreadsheet program that can sort and classify huge amounts of data. While Microsoft Excel 2007 comes with many built in functions, it doesn't have a built in function to convert Julian dates. Julian dates are often used in computer software, and are a combination of a two-digit year and three-digit number representing the day of the year. For example, 99001 is January 1, 1999. While Julian dates are easy for a computer to read, they aren't as easy for you to decipher. Excel allows you to convert between Julian and regular dates with the addition of a formula into your spreadsheet.

Skill level:


  1. 1

    Click on the cell were you would like your output data to start. For example, if you have a list of Julian dates in column A, you might want your regular dates to start on column B, so click on cell B1.

  2. 2

    Enter the following formula into the cell. =("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1. The formula returns regular dates for the A column: tailor the formula to your cell range. For example, if you data is in column B, change the "A"s in the formula to "B"s.

  3. 3

    Copy the formula into as many cells as you want dates outputted. For example, if you have Julian dates in cells A1 through A50, copy the formula 50 times from cells B1 to B50.

  4. 4

    Highlight the cells you created in Step 3.

  5. 5

    Right-click on the highlighted cells and choose "Format Cells."

  6. 6

    Click on "Date" from the "Number" tab. This tells Excel to convert the formula's output into regular calendar dates. Choose the date type (for example, 14 December, 2009 or 14 Dec) and click "OK."

Tips and warnings

  • A fast way to copy cells is to drag the fill tool (the little black square in the bottom right corner of the cell) across the cells you want the formula copied to.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.