Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Last row number in range
  • Find closest match in Excel
  • How to reference named range different sheet in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to Sort by Color in Excel
  • How to count table rows in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Reverse List in Excel
  • Randomize/ Shuffle List in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days

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.

Worked Example:   Calculate retirement date in Excel

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

Worked Example:   Get age from birthday in Excel

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

Worked Example:   Calculate years between dates in Excel

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:

How to calculate nth day of year in Excel

Next Post:

How to calculate nth day of week in month in Excel

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
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • How to check if cell contains one of many things in Excel
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • T function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal minutes to time in Excel
  • Get days, hours, and minutes between dates in Excel
  • Pad week numbers with zeros in Excel
  • Count birthdays by month in Excel
  • How to calculate most recent day of week in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
  • Example of Future value of annuity in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles