Excel Formula: Convert Date to Julian
Sometimes, you need to convert the standard date to the Julian date in Excel, here this article introduces a formula which combines the TEXT, YEAR and DATE functions to solve this job.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
TEXT(date,"yy")&date-(DATE(YEAR(date),1,0)) |
Arguments
Date: the date you want to convert to Julian date. |
Return Value
This formula returns a Julian date in a text(yyd) format. First two digits represent the two-digits year, the else numbers represent the number of days.
How this formula work
A list of dates are in cell B3:B6, to convert them to Julian date, please use below formula:
=TEXT(B3,"yy")&B3-(DATE(YEAR(B3),1,0)) |
Press Enter key, then drag autofill handle down to apply this formula.
Explanation
TEXT function converts a number to a text in a specific format. As we know, the date is stored as a number in Excel, here the TEXT function converts the date to text in the yy format.
YEAR function gets the year from a date.
DATE function combines year, month, and day numbers from separate cells to a valid date. Here the formula
- DATE(YEAR(B3),1,0)
- =DATE(2020,1,0)
- =12/31/2019
Tip: if the day number is less than 1, it will subtract absolute number of days from the first day of the month.
- B3-(DATE(YEAR(B3),1,0))
- =3/12/2020-12/31/2019
- =72
Note
If you want to get the Julian date in 4-digit year and 3-digit day number, please use below formula
=TEXT(B3,"yyyy")&TEXT(B3-DATE(YEAR(B3),1,0),"000")
Relative Formulas
- Calculate Difference Between Two Dates
Here in this tutorial, it introduces the formulas on calculating difference between two dates in years, months, weeks, and days. - Convert date string to datetime
Sometimes, while you import or paste some datetime data from other devices to Excel, the datetimes maybe not be recognized as proper dates... - Calculate Days Remaining Between Two Dates
Here, this tutorial provides a formula to quickly calculate the left days between two dates. - Calculate Hours Minutes Seconds Between Two Times
Here in this tutorial, it introduces some formulas to quickly present the time difference result as single time unit.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.