How To Get The Percent Of The Year Completed

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 qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃