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

Data Analysis

  • Error Bars in Excel
  • How To Insert and Customize Sparklines in Excel
  • How To Compare Two Lists in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to create Gauge Chart in Excel

References

  • How to get address of first cell in range in Excel
  • Offset in Excel
  • Left Lookup in Excel
  • How to use Excel ROWS Function
  • How to use Excel LOOKUP Function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Excel Data validation date in specific year
  • Excel Data validation number multiple 100
  • Excel Data validation must contain specific text

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:   YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

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 retirement date 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

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • How to use Excel FALSE Function
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function

Date Time

  • Extract date from a date and time in Excel
  • Add days exclude certain days of week in Excel
  • Get month name from date in Excel
  • Get date from day number in Excel
  • Dynamic date list in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel

General

  • Hide and Unhide Columns or Rows in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • Count cells that do not contain errors in Excel
  • Count cells that do not contain many strings in Excel
  • How to calculate project complete percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning