Part II: Working with Formulas and Functions
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)
Note that in our Julian date formula, we are using a zero as our day argument. When you
use 0 as the day argument, you are telling Excel that you want the day before the 1st of
the given month. In this example, the day before January 1 is December 31. For instance,
entering this formula into a blank cell will return December 31, 1959:
=DATE(1960,1,0)
Joining our two formulas together with an ampersand brings the Julian date together:
=RIGHT(YEAR(A4),2)& A4-DATE(YEAR(A4),1,0)
Calculating the percent of year completed and remaining
When building Excel reports and dashboards, oftentimes it’s beneficial to calculate the per-
cent of the year that has elapsed and what percent remains. These percentages can be used
in other calculations or simply as a notification for your audience.
Figure 12.9 shows a sample of this concept. Note in the Formula bar that we are using the
YEARFRAC function.
FIGURE 12.9
Calculating the percent of the year completed
The YEARFRAC function simply requires a start date and an end date. Once it has those two
variables, it calculates the fraction of the year representing the number of days between
the start date and end date.
=YEARFRAC(B3,C3)
To get the percent remaining, as shown in cell C7 of Figure 12.9, simply subtract 1 from the
YEARFRAC formula:
=1-YEARFRAC(B3,C3)
