A percentage value is a great way to express a proportion’s size in whole. You may want to calculate and see what percent of the year is completed. In this article, we are going to show you how to calculate the percent of completed year in Excel.
Formula to calculate the percent of completed year
* CurrentDate represents the date value or reference for the date you want to calculate.
How It Works
To calculate the percent of completed year, you need 3 Excel functions:
The YEARFRAC function does the main job. It can return the fraction of a year between two specified dates. Obviously, the latter of the two dates is the date we want to calculate. The, initial date, on the other hand, should be the last day of the previous year.
To populate the last day of the previous year, we can use DATE function which returns a date number based on given year, month and day arguments. Because month and day values are static, we can simply use 12 and 31 respectively.
For year, the YEAR function will help us to get the year of given date. To find the previous year, subtracting by 1 will be enough. For example, if the given date is “9.9.2020”, then the previous year is 2019 (2020 - 1).
The YEARFRAC function has one more optional argument which determines the type of the count basis. This is a predefined value. Thus, you can use only the following values that Excel allows.
| Basis | Day count basis |
| 0 or omitted | US (NASD) 30/360 – All months are 30 days and the year is 360 days by US convention. |
| 1 | Actual/actual – Calculates by real-time day counts including leap years. |
| 2 | Actual/360 – A year is 360 days. |
| 3 | Actual/365 – A year is 365 days. |
| 4 | European 30/360 – All months are 30 days and the year is 360 days by European convention. |
