How To Get The Percent Of The Year Completed

2017-09-30

Example

=YEARFRAC(DATE(YEAR(B3)1,12,31),B3,1)

Generic Formula

=YEARFRAC(DATE(YEAR(Date)1,12,31),Date,1)
Date – This is the date on which you would like to calculate the percentage of the year completed.

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.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This