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

Data Analysis

  • How to combine 2 or more chart types in a single chart in Excel
  • Subtotal function in Excel
  • How to count table columns in Excel
  • Working With Tables in Excel
  • How To Remove Duplicates In Excel Column Or Row?

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Basic INDEX MATCH approximate in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Lookup entire row in Excel
  • How to use Excel INDIRECT Function

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation no punctuation
  • How To Create Drop-down List in Excel
  • Excel Data validation date in specific year
  • Excel Data validation number multiple 100

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

  • Not Equal To ‘<>‘ operator in Excel
  • Invoice status with nested if in Excel
  • How to use Excel FALSE Function
  • Nested IF function example in Excel
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • Find Last Day of the Month in Excel
  • How to get workdays between dates in Excel
  • Get project midpoint in Excel
  • Convert date to Julian format in Excel
  • Calculate number of hours between two times in Excel

Grouping

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

General

  • Excel Operators
  • Excel Ribbon Quick Overview For Beginners
  • Convert column number to letter in Excel
  • Hide and Unhide Columns or Rows in Excel
  • Count cells that do not contain many strings in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning