Example
Generic Formula
What It Does
This formula will return the percent of the year completed for a given date.
How It Works
The YEARFRAC function will take two dates and return the fraction of the year between them. We will use this function with the our given date and the previous December 31st date to get the desired fraction of the year. We can determine what the previous December 31st date is by using the DATE and YEAR functions. YEAR(Date) will give us the year of our date. In our example, YEAR(“2017-01-31”) results in 2017 which is the current year. Then we subtract 1 to get the previous year of 2016. We can then construct an Excel date serial number for the previous year’s December 31st by using DATE(Year-1,12,31). In our example, DATE(2017-1,12,31) results in the date “2016-12-31“. Now we can determine the fraction of the year completed with YEARFRAC(“2016-12-31″,”2017-01-31”,1), which results in 31/365 = 8.5%. Note that the last 1 in the formula is a predefined Excel input that tells the YEARFRAC function to use the actual number of days between the two dates and the actual number of days in the year. Here are the other possible input options.
0 – Calculates the fraction assuming US (NASD) 30/360 accounting method.1 – Calculates the fraction using the actual number of days between the two dates and the actual number of days in the year.2 – Calculates the fraction using the actual number of days between the two dates and assumes a 360 day year.3 – Calculates the fraction using the actual number of days between the two dates and assumes a 365 day year.4 – Calculates the fraction assuming European 30/360 accounting method.
