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.

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.

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!

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

 Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

60-Day Unconditional Money-Back Guarantee

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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back Guarantee

No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thanks Sunny the formula worked perfect...I received it over a month ago....I appreciate it....its a big help...God Bless you
This comment was minimized by the moderator on the site
Can anyone help me convert a backwards formatted Julian date code? For example 25922 is September 16th, 2022.
This comment was minimized by the moderator on the site
Hi, DT, try below formula:
=DATE(IF(0+(RIGHT(A1,2))<30,2000,1900)+RIGHT(A1,2),1,LEFT(A1,3))
then format the result as the date formatting as you need.
This comment was minimized by the moderator on the site
Sunny-
That revised formula worked perfect! thanks for your help...I appreciate it.

Tony
This comment was minimized by the moderator on the site
Sunny-
Thanks for the reply, in order to get a 4-digit number, I last digit of the year would suffice (i.e. 2022=2 or 2023=3).
If you have any other ideas on how to modify the formula let me know...and thanks again for your response.
This comment was minimized by the moderator on the site
Hi, Tony, try this formula =RIGHT(TEXT(I1,"yy")&TEXT((I1-DATEVALUE("1/1/"&TEXT(I1,"yy"))+1),"000"),4), I1 is the date you want to convert.
This comment was minimized by the moderator on the site
Hi-
Thank you for the excel formula...that converts a Calander date to a Julian date =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

Your formula displays 5 digits for the Julian date....I'd like to change in the formula to get 4 digits for the Julian date. What should I do to get this outcome?

Thanks

Tony
This comment was minimized by the moderator on the site
Hi, Tony, the formula TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") returns a 5-digit number, first two number indicate the year, last three number indicate the nth day of the year, 5 numbers combined to indicates the date. If you just want 4-digit number, I do not know which number you want to ignore.
This comment was minimized by the moderator on the site
Si tienes Office en español, puedes utilizar la siguiente formula =TEXTO(DE2,"AA")&TEXTO((DE2-FECHANUMERO("1/1/"& TEXTO(DE2,"AA"))+1),"000") donde DE2 es la celda donde esta tu fecha gregoriana. Saludos
This comment was minimized by the moderator on the site
Ex: 2019181
This comment was minimized by the moderator on the site
=DATE(INT(A1/1000),1,MOD(A1,1000))
This comment was minimized by the moderator on the site
=DATE(INT(A1/1000),1,MOD(A1,1000))
This comment was minimized by the moderator on the site
Can anyone help me convertig 6 digital julian date to normal date ?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Tried with formulae given in the post, but not working.
Output is as something like 2017Tue
This comment was minimized by the moderator on the site
Sorry I cannot get your problem clearlly? Could you upload a screenshop of your file to me for sovling? Thank u.
This comment was minimized by the moderator on the site
Hey Mister, I am getting incorrect values when the year is a leap year. Thanks though, I am grateful for this.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
I've just worked out what the confusion is. There are two calendars at play here, the Julian calendar introduced by Julius Caesar in 45 BCE and gradually replaced by the Gregorian calendar over the last 400 years (there were some holdouts). the Julian Day (JDN) is a separate but related system used by astronomers and is just a continuous count of the number of days since noon on January 1st 4713 BCE as described by John Murrell above. What the main article is talking about is a simplified version of the same idea used for military food packaging but the count resets every year. So that's three dating systems calling themselves the Julian System.
This comment was minimized by the moderator on the site
This is very thorough helpful information. Thanks for posting.
There are no comments posted here yet