Excel Calculate Percentage of Year Completed
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To calculate the percentage of the year completed in Excel, use the formula =YEARFRAC(DATE(YEAR(TODAY()),1,1),TODAY()) and format the result as a percentage. This returns the fraction of the current year that has elapsed, automatically updating each day. For a specific date, replace TODAY() with that date.
Formula
The core formula uses YEARFRAC(start_date, end_date, [basis]), which returns the fraction of a year between two dates. For a percentage, multiply by 100 or format the cell as a percentage. The syntax is:
- start_date – the first day of the year (e.g.,
DATE(YEAR(TODAY()),1,1)). - end_date – the date for which you want the completion (e.g.,
TODAY()). - basis – optional day-count convention; 1 (actual/actual) is common, 3 (actual/365) gives a simpler 365-day year.
If you prefer a manual calculation, use =(end_date - start_date) / 365 and format as percentage. This treats every year as 365 days, which is accurate enough for most business uses.
Worked Example
Assume today is June 30, 2025. Using the default values from the calculator (start = Jan 1, 2025, end = Dec 31, 2025), the days elapsed is 181 (including the start date). The percentage is 181 / 365 = 0.4959, or 49.59%. The calculator shows 49.59% because it uses a 365-day year. With YEARFRAC and basis 3, the result is identical. If you used basis 1, you would get 49.86% because it accounts for the actual number of days in the year (365 in 2025).
How to Use the Formula in Excel
- Enter a date in a cell, e.g.,
A1 = 2025-06-30. - In another cell, type
=YEARFRAC(DATE(YEAR(A1),1,1),A1). - Press Enter, then right-click the cell and choose Format Cells > Percentage.
- To make it dynamic, replace
A1withTODAY().
For a project that starts mid-year, use the project start date as the start_date. For example, a project from March 15 to June 30 gives =YEARFRAC(DATE(2025,3,15),DATE(2025,6,30)), which returns 0.2959 (29.59%).
Common Mistakes and How to Avoid Them
- Using the wrong date format – Excel may interpret text dates as strings. Use
DATE()or valid date cells. - Forgetting to format as percentage – The result shows as a decimal (0.5) unless you apply percentage formatting.
- Leap years – A 365-day formula will be off by one day in leap years. Use
YEARFRACwith basis 1 to be exact. - Including the end date incorrectly – For “days completed,� decide whether to include the current day. The formula above includes it.
Why Use YEARFRAC Instead of Simple Division?
Simple division (end - start)/365 is easy to read but ignores leap years. YEARFRAC offers several day-count conventions: basis 0 (US 30/360), 1 (actual/actual), 2 (actual/360), 3 (actual/365), and 4 (European 30/360). For year completion, basis 1 or 3 are most appropriate. Basis 1 gives the most accurate fraction because it uses the actual number of days in the year, which is 366 in leap years. This matters for financial calculations, project tracking, or any metric that must be exact.
How to Read the Result
The percentage tells you how much of the year has passed as of the given date. For example, 50% means half the year is done. You can use this for progress reports, annual goal tracking, or calculating prorated amounts. If you need the remaining percentage, subtract from 100%: =1 - YEARFRAC(...).
Limitations and Alternatives
The formula assumes a linear time progression. It does not account for business days, holidays, or working hours. For workdays, use NETWORKDAYS to count weekdays only. For a fiscal year that doesn’t start on January 1, change the start_date accordingly. For example, a fiscal year starting July 1: =YEARFRAC(DATE(YEAR(TODAY()),7,1),TODAY()) (but adjust for the correct year if the fiscal year spans two calendar years).
Frequently Asked Questions
How do I calculate the percentage of the year completed for a past date?
Replace TODAY() with the specific date. For example, =YEARFRAC(DATE(2025,1,1),DATE(2025,3,15)) gives 0.2027 (20.27%).
Does the formula work for leap years?
Yes, if you use YEARFRAC with basis 1. Simple division by 365 will be off by one day in a leap year. For example, from Jan 1 to Dec 31, 2024 (leap year) with basis 1 gives 1.0, while simple division gives 366/365 = 1.0027.
How do I show the percentage with no decimals?
Use =ROUND(YEARFRAC(...)*100,0) or format the cell to show zero decimal places. For instance, 49.59% becomes 50%.
Can I calculate the percentage of a quarter or month completed?
Yes. For a quarter, use =YEARFRAC(DATE(YEAR(TODAY()),CHOOSE(QUARTER(TODAY()),1,4,7,10),1),TODAY()). For a month, use =DAY(TODAY())/DAY(EOMONTH(TODAY(),0)).
What if my year starts on a date other than January 1?
Simply change the start_date in the formula. For example, a school year starting September 1: =YEARFRAC(DATE(YEAR(TODAY()),9,1),TODAY()). Make sure to handle years that cross January 1 correctly, possibly using DATE(YEAR(TODAY())-1,9,1) if the current date is before September.