Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Understanding Pivot Tables in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Get column index in Excel Table
  • How To Load Analysis ToolPak in Excel
  • How to calculate average last N values in a table in Excel

References

  • How to get first column number in range in Excel
  • How to get last row in numeric data in Excel
  • How to use Excel INDIRECT Function
  • How to create dynamic named range with OFFSET in Excel
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation require unique number

How to calculate percent of year complete in Excel

by

If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function.

Formula

=YEARFRAC(DATE(YEAR(date),1,1),date)

Explanation

In the example, the formula in C5, which calculates percentage of year remaining, is:

=YEARFRAC(DATE(YEAR(B5),1,1),B5)

How the formula works

the YEARFRAC function takes two dates, a start date and and an end date, and calculates the time between them in fractional years. In this case, we are using the end date as-is, but generating a start date that equals the first day of the same year with this bit of code:

DATE(YEAR(B5),1,1)

This extracts the year value from the date in B5 and plugs it into the DATE function, along with a 1 for both month and day.

The result is that the YEARFRAC function returns a decimal value that corresponds to the fraction of the year between the two dates.

When this value is formatted with the percentage number format, it shows percent of year complete.

Percent of year remaining

To calculate percent of year remaining, just adjust the formula to subtract the fractional year from 1. In the example, D5 contains this formula:

=1-YEARFRAC(DATE(YEAR(B5),1,1),B5)

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Generate series of dates by weekends in Excel
  • Display Days in month in Excel
  • Get days before a date in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into 3 hour buckets in Excel
  • Calculate conditional mode with criteria in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • Running count group by n size in Excel

General

  • How to create dynamic named range with INDEX in Excel
  • Find, Select, Replace and Go To Special in Excel
  • Count cells that contain errors in Excel
  • How to calculate percent change in Excel
  • Create dynamic workbook reference to another workbook in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning