Julian dates are often used in manufacturing environments as a timestamp and quick reference for a batch number. This type of date coding allows retailers, consumers, and service agents to identify when a product was made, and thus the age of the product. Julian dates are also used in programming, the military, and astronomy.
Different industries have their own variations on Julian dates, but the most commonly used variation is made up of two parts: a two-digit number representing the year, and the number of elapsed days in the year. For example, the Julian date for 1/1/1960 would be 601. The Julian date for 12/31/2014 would be 14365.
Excel has no built-in function to convert a standard date to Julian date, but the figure illustrates how you can use the following formula to accomplish the task.
=RIGHT(YEAR(A4),2)& A4-DATE(YEAR(A4),1,0)
This formula is really two formulas joined as a text string using the ampersand (&).
The first formula uses the RIGHT function to extract the right two digits of the year number. Note that you use the YEAR function to pull out the year portion from the actual date.
=RIGHT(YEAR(A4),2)
The second formula is a bit trickier. For this one, you have to find out how many days have elapsed since the beginning of the year. To do so, you first need to subtract the target date from the last day of the previous year:
A4-DATE(YEAR(A4),1,0)
Note the use of the DATE function.
The DATE function allows you to build a date on the fly using three arguments: the year, the month, and the day. The year can be any whole number from 1900 to 9999. The month and date can be any positive or negative number.
For example, this formula would return the date serial number for December 1, 2013:
=DATE(2013, 12, 1)
In the Julian date formula in this example, you use a zero as the day argument. When you use 0 as the day argument, you tell Excel that you want the day before the first day of the given month. So, for instance, entering the following formula into a blank cell will return December 31, 1959:
=DATE(1960,1,0)
Joining the two formulas together with an ampersand builds a Julian date made up of the first two characters of the year and the number of elapsed days:
=RIGHT(YEAR(A4),2)& A4-DATE(YEAR(A4),1,0)
