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


arrow blue right bubble 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:
doc-convert-julian-date-1

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.
doc-convert-julian-date-2

3. Click OK, then you can see all Julian dates have been converted to calendar dates.
doc-convert-julian-date-3


arrow blue right bubble 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.
doc-convert-julian-date-4
Tip: A1 is the calendar date you want to convert to Julian date.


Quickly convert nonstandard date to standard date formattiing(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!
doc convert date
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Relative Articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ishant · 2 months ago
    Ex: 2019181
    • To post as a guest, your comment is unpublished.
      rumesh.combank@gmail.com · 1 months ago
      =DATE(INT(A1/1000),1,MOD(A1,1000))
    • To post as a guest, your comment is unpublished.
      Rumesh · 1 months ago
      =DATE(INT(A1/1000),1,MOD(A1,1000))
  • To post as a guest, your comment is unpublished.
    Ishant · 2 months ago
    Can anyone help me convertig 6 digital julian date to normal date ?
  • To post as a guest, your comment is unpublished.
    Cliff Johnston · 1 years ago
    Hi, there is a problem when you try to convert Julian date to calendar date with dates of 2001 thru 2009. Any suggestions? Example 1/1/2001 = JD of 01001 which is correct. However if you use the formula JD of 01001 to Calendar Date conversion the answer is 1/1/2010. Where is this error?
  • To post as a guest, your comment is unpublished.
    Satish · 2 years ago
    Tried with formulae given in the post, but not working.
    Output is as something like 2017Tue
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry I cannot get your problem clearlly? Could you upload a screenshop of your file to me for sovling? Thank u.
  • To post as a guest, your comment is unpublished.
    Jonathan · 3 years ago
    Hey Mister, I am getting incorrect values when the year is a leap year. Thanks though, I am grateful for this.
  • To post as a guest, your comment is unpublished.
    John Murrell · 3 years ago
    Total rubbish the definition of Julian date is:
    The Julian Day Number (JDN) is the integer assigned to a whole solar day in the Julian day count starting from noon Universal time, with Julian day number 0 assigned to the day starting at noon on January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar),[1][2][3] a date at which three multi-year cycles started (which are: Indiction, Solar, and Lunar cycles) and which preceded any historical dates.[4] For example, the Julian day number for the day starting at 12:00 UT on January 1, 2000, was 2,451,545.[5]

    A modified version of the Julian date denoted MJD obtained by subtracting 2,400,000.5 days from the Julian date JD, The MJD therefore gives the number of days since midnight on November 17, 1858.

    There are inherent problems with conversion into Excel due to the fact that has a leap year error (in 1900??) also astronomers who use JD have a year zero which does not exist in the normal civil calendar which goes from 1BC to 1AD with no year zero. Historically correct but bad for calculations
    • To post as a guest, your comment is unpublished.
      Steve · 2 years ago
      The military writes the Julian day out like this. The first 2 digits are the last 2 digits of the current year and the next 3 digits are the day corresponding to the 365/366 calendar day.
    • To post as a guest, your comment is unpublished.
      joe · 3 years ago
      This is very thorough helpful information. Thanks for posting.