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.
|Date: the date you want to convert to Julian date.|
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：
Press Enter key, then drag autofill handle down to apply this formula.
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
Tip: if the day number is less than 1, it will subtract absolute number of days from the first day of the month.
If you want to get the Julian date in 4-digit year and 3-digit day number, please use below formula
- 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.
- 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.