Chapter 12: Using Formulas with Dates and Times
12
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
- The Julian date for 12/31/2014 would be 14365.
Excel has no built-in function to convert a standard date to a Julian date, but Figure 12.8
illustrates how a formula can be used to accomplish the task.
=RIGHT(YEAR(A4),2)& A4-DATE(YEAR(A4),1,0)FIGURE 12.8
Converting standard dates into Julian dates
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 num-
ber. Note we are using the YEAR function to pull out the year portion from the actual date.
=RIGHT(YEAR(A4),2)For more details on using the RIGHT function, see the section called “Extracting parts of a text string”
in Chapter 11.The second formula is a bit trickier. Here we have to find out how many days have elapsed
since the beginning of the year. For this, we first need to subtract the target date from the
last day of the previous year.
A4-DATE(YEAR(A4),1,0)You’ll note the use of the DATE function. The DATE function allows us to build a date on
the fly using three arguments: the year, the month, and the day. The year can be any whole