Explanation

This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula:

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

The result is nth day of the year, based on the date in cell B5. Notice the day argument in the DATE function is supplied as zero. A nice feature of DATE is it can handle day values that are “out of range” and adjust the result appropriately. When we give DATE a year for year , a 1 for month, and a zero for day , the DATE function returns the last day of the previous year:

DATE(YEAR(B5),1,0) // last day of previous year

So, the formula is solved like this

=B5-DATE(YEAR(B5),1,0)
=B5-DATE(2018,1,0)
=43109-43100
=9

nth day this year

To adjust the formula to return the nth day of year for the current date, just use the TODAY function for the date:

=TODAY()-DATE(YEAR(TODAY()),1,0)

The logic of the formula remains the same, but the date values are supplied by the TODAY function.

Explanation

The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are halfway* through the year.

*By default, the YEARFRAC function uses a 30/360-day convention, assuming a year contains twelve 30-day months = 360 days. This is controlled by an optional argument called “basis” .

The core of the formula is the YEARFRAC function , which returns the fractional years between two dates as a decimal value. The formula in C5, copied down, is:

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

YEARFRAC takes two dates: a start date and an end date:

=YEARFRAC(start, end)

We calculate the start date with the DATE function and YEAR function like this:

DATE(YEAR(B5),1,1) // returns first of year

The YEAR function extracts the year from the date in B5:

YEAR(B5) // returns 2021

The number is returned directly to the DATE function with the number “1” hard-coded for both month and day arguments :

=DATE(2021,1,1) // returns 1-Jan-2021

The end date is the date given in column B. With January 15, 2021, in B5, the formula is evaluated like this:

=YEARFRAC(DATE(YEAR(B5),1,1),B5)
=YEARFRAC(DATE(2021,1,1),B5)
=YEARFRAC(44197,44211)
=0.0388888888888889
=4%

Note: the large serial numbers in Step 3 above are Excel dates in their raw format.

The YEARFRAC function returns the decimal value 0.0388888888888889. Then this value is formatted with the Percentage number format which displays as a percentage.

Current year and date

To return the percent of the year completed based on the current year and current date, you can adjust the formula to use the TODAY function like this:

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

In this version, the TODAY function returns the current date which goes into YEARFRAC twice – once as the start date, and once as the end date. Note the start date uses the DATE function to create a date for the first of the year, by hard-coding the number 1 for month and day and extracting the current year with the YEAR function . The result is the percentage of the year completed through as of today. This percentage will continually update over time.

Percent of the year remaining

To calculate the percent of the year remaining instead of the percent complete, you can adjust the formula to subtract the fractional year from “1”. In the example shown, D5 contains this formula copied down:

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

Formatting percentages in Excel

In mathematics, a percentage is a number expressed as a fraction of 100. For example, 35% is read as “thirty-five percent” and is equivalent to 35/100 or 0.35. Accordingly, the values in columns C and D are decimal values with the Percentage number format applied.