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.

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 ðŸ˜ƒ

Related Posts

8 Ways to Apply a Formula to an Entire Column in Microsoft Excel

Are you wondering how to apply a formula to an entire column in Excel? Read...

5 Ways to Lock and Unlock Formulas in Microsoft Excel

Do you want to lock the formulas in your Excel sheet to prevent other users...

9 Ways to Show Formulas in Microsoft Excel

This post is going to show you all the different ways you can show the...