Excel Calculate Percentage of Year: Formula & Steps

To calculate the percentage of the year that has elapsed in Excel, you divide the number of days that have passed by the total number of days in the year (365 or 366) and format the result as a percentage. For example, if today is day 200 of a 365-day year, the formula returns 54.8% — meaning 54.8% of the year is complete. This article explains the formulas, how to handle leap years, and common pitfalls.

Formula

The basic formula for the percentage of the year completed up to a specific date is:

Percentage = (Day of Year / Total Days in Year) × 100

Where:

• Day of Year – the number of days from January 1 to the given date, inclusive. In Excel, you can get this with =A1-DATE(YEAR(A1),1,1)+1.
• Total Days in Year – 365 for a common year, 366 for a leap year. In Excel, use =DATE(YEAR(A1),12,31)-DATE(YEAR(A1),1,1)+1.

If you want the percentage remaining, subtract the result from 100. The formula above in the calculator uses the start and end dates you enter, but for a single date, replace “end� with the date you’re checking.

Worked Example

Using the default dates from the calculator: start = January 1, 2024, end = December 31, 2024. That’s the entire year, so the percentage should be 100%.

1. Day of year for Dec 31, 2024: DATE(2024,12,31) - DATE(2024,1,1) + 1 = 366 (2024 is a leap year).
2. Total days in 2024: DATE(2024,12,31) - DATE(2024,1,1) + 1 = 366.
3. Percentage = (366 / 366) × 100 = 100%.

The calculator returns 100.00%, as expected. If you entered March 1, 2024, the day of year is 61, so the percentage would be (61/366)×100 ≈ 16.67%.

How to Write the Formula in Excel

Assuming your date is in cell A1, here’s the most direct formula:

= (A1 - DATE(YEAR(A1),1,1) + 1) / (DATE(YEAR(A1),12,31) - DATE(YEAR(A1),1,1) + 1)

Format the cell as a percentage (Home → Number → %). This works for any date, automatically adjusting for leap years because the denominator uses the actual last day of the year.

If you want the percentage of the year remaining, use =1 - previous_formula.

Leap Year Handling

Leap years have 366 days, so the denominator changes. The formula above handles this automatically because DATE(YEAR(A1),12,31) returns Dec 31 of that specific year, which is day 366 in leap years and day 365 otherwise. Never hardcode 365 or 366; always use the dynamic denominator to avoid errors.

Common Mistakes and Misconceptions

• Off-by-one errors: Forgetting to add 1 to the day-of-year calculation. January 1 is day 1, not day 0. If you omit the +1, January 1 gives 0% instead of 0.27%.
• Hardcoding 365: Using 365 as the denominator in a leap year gives slightly wrong results (e.g., Dec 31, 2024 would show 100.27% instead of 100%).
• Using TODAY() incorrectly: If you want the percentage as of today, use =TODAY() in the formula, but remember it updates every day. For a fixed date, enter the date directly.
• Formatting as number instead of percentage: The result is a decimal (0.548), so you must format as % to see 54.8%. If you leave it as a number, you’ll see 0.55.

Alternatives and Related Calculations

Instead of a formula, you can use Excel’s built-in YEARFRAC function. =YEARFRAC(DATE(YEAR(A1),1,1), A1) returns the fraction of the year between two dates, but note that it uses a day-count basis (default US 30/360) which may not match the actual day count. For a true calendar-day percentage, the manual formula is more accurate.

Related calculations include:

• Percentage of month elapsed: =DAY(A1)/DAY(EOMONTH(A1,0))
• Percentage of quarter elapsed: More complex, but you can calculate days in the quarter using MOD and date functions.
• Days remaining in year: =DATE(YEAR(A1),12,31)-A1

Frequently Asked Questions

Does the formula work for dates in different years?

Yes, because the formula uses YEAR(A1) to reference the year of the date in question. Each date is evaluated against its own year, so you can use it for any date from any year.

How do I show the percentage as a whole number?

Format the cell as a percentage and set decimal places to 0, or use =ROUND(formula*100,0) and append the % symbol manually. The latter changes the value to a number, so it’s better to keep the formula and adjust formatting.

What if I want the percentage of the year that has passed including today?

Use =TODAY() in place of A1. The formula will update daily. For example, =(TODAY()-DATE(YEAR(TODAY()),1,1)+1)/(DATE(YEAR(TODAY()),12,31)-DATE(YEAR(TODAY()),1,1)+1).

Why does my result show as 0.55 instead of 55%?

Because the cell is formatted as a number, not a percentage. Select the cell, go to Home → Number → Percent Style (or press Ctrl+Shift+%). The underlying value is correct; only the display format needs changing.

Can I calculate the percentage of the year for a date in the future?

Yes, the formula works for any date, past or future. For a future date, it will return a percentage greater than 100% if the date is after Dec 31 of that year? No, actually the formula uses the same year, so a date in December of that year gives a value up to 100%. A date in the next year would be treated as belonging to that next year, so it would start over. For a date beyond Dec 31, you’d need to adjust the year reference.

Is there a way to calculate the percentage of the year without using the DATE function?

You could use YEARFRAC with basis 1 (actual/actual) for a close approximation, but it may differ slightly due to leap year conventions. The DATE method is the most precise and transparent.

Related

• Home Screen Icon Control: Move, Hide & Organize
• Pip Version Operators: How They Limit Releases
• How to Control Gall Aphids Without Harming Beneficial Insects

in start | Start date | - | 2024-01-01 | 0
in end | End date | - | 2024-12-31 | 0
out pct_year | Percentage of year | % | (end - start + 1) / (DATE(YEAR(end),12,31) - DATE(YEAR(end),1,1) + 1) * 100 | 2