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.

Explanation

In this example, the goal is to calculate the previous Sunday based on any given date. At a high level, this means we need to subtract some number of days from the given date. For example, if the given date is a Monday, we need to subtract 1 day. If the given date is a Tuesday, we need to subtract 2 days, and so on. The main challenge is to figure out how many days to subtract and for this, we use the WEEKDAY function .

Note: the formulas below use simple math to adjust the date. This works because Excel dates are large serial numbers .

WEEKDAY solution

The WEEKDAY function accepts a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable, based on a second argument called return_type . In this example, because we want to roll back the date to the previous Sunday, we want to set WEEKDAY to return 1 for Monday and 7 for Sunday. To do this, we can use either 2 or 11 for return_type:

=WEEKDAY(A1,2) // returns 1 for Monday
=WEEKDAY(A1,11) // returns 1 for Monday

Now that we have WEEKDAY returning 1 for Monday and 7 for Sunday, we can simply subtract the result from the given date:

=B5-WEEKDAY(B5,11)

If the date is a Sunday, WEEKDAY will return 7. Subtracting 7 from the original date in B5 will return the previous Sunday. If the date is a Tuesday, weekday will return 2, moving the date back 2 days to Sunday. The formula works the same way for each day of the week.

Other previous weekdays

In the above formula, you might wonder why we’re using 11 for return_type instead of 2? This is done for consistency To adapt the formula to return different previous weekdays, return_type needs to be adjusted Using 11 allows the different values to “line up” logically, as seen below:

=A1-WEEKDAY(A1,11) // previous Sunday
=A1-WEEKDAY(A1,12) // previous Monday
=A1-WEEKDAY(A1,13) // previous Tuesday
=A1-WEEKDAY(A1,14) // previous Wednesday
=A1-WEEKDAY(A1,15) // previous Thursday
=A1-WEEKDAY(A1,16) // previous Friday
=A1-WEEKDAY(A1,17) // previous Saturday

To be clear, using 2 instead of 11 for the previous Sunday will work just fine.

CHOOSE function alternative

One feature of all formulas above is that they subtract days even when the given date is already the target weekday. In other words, if the date is already a Sunday, the result is the Sunday seven days prior. This isn’t always desired behavior. Sometimes, the goal is to leave the date alone if it’s already the right day of week. One way to accomplish this is with the CHOOSE function .

The CHOOSE function is used to select values by numeric position. For example, if we have the colors “red”, “blue”, and “green”, we can use CHOOSE like this:

=CHOOSE(1,"red", "blue", "green") // returns "red"
=CHOOSE(2,"red", "blue", "green") // returns "blue"
=CHOOSE(3,"red", "blue", "green") // returns "green"

The first argument is called index_num and specified the index of the value to return. We can combine CHOOSE and WEEKDAY to give us the correct “roll back” number like this:

=CHOOSE(WEEKDAY(date),0,1,2,3,4,5,6) // rollback calculation

Here, the index_num argument is provided by the WEEKDAY function. Note WEEKDAY is in its default mode (no return_type provided), which returns 1 for Sunday and 7 for Saturday. The values that follow WEEKDAY are the actual rollback numbers. The first value is zero, which is the number of days to roll back if the date is already a Sunday. Putting the entire formula together, we get:

=B5-CHOOSE(WEEKDAY(B5),0,1,2,3,4,5,6) // previous Sunday

This formula will return the previous Sunday unless the date is already a Sunday. In that case, CHOOSE will return zero and the formula itself will return the original date unchanged. To extend this idea to handle other days of the week, you can adjust the rollback numbers as needed for each day of the week like this:

=date-CHOOSE(WEEKDAY(date),0,1,2,3,4,5,6) // prev Sun
=date-CHOOSE(WEEKDAY(date),6,0,1,2,3,4,5) // prev Mon
=date-CHOOSE(WEEKDAY(date),5,6,0,1,2,3,4) // prev Tue
=date-CHOOSE(WEEKDAY(date),4,5,6,0,1,2,3) // prev Wed
=date-CHOOSE(WEEKDAY(date),3,4,5,6,0,1,2) // prev Thu
=date-CHOOSE(WEEKDAY(date),2,3,4,5,6,0,1) // prev Fri
=date-CHOOSE(WEEKDAY(date),1,2,3,4,5,6,0) // prev Sat

Alternately, you can adjust the formula to use a return_type argument in WEEKDAY and leave the rollback values alone:

=date-CHOOSE(WEEKDAY(date,17),0,1,2,3,4,5,6) // prev Sun
=date-CHOOSE(WEEKDAY(date,11),0,1,2,3,4,5,6) // prev Mon
=date-CHOOSE(WEEKDAY(date,12),0,1,2,3,4,5,6) // prev Tue
=date-CHOOSE(WEEKDAY(date,13),0,1,2,3,4,5,6) // prev Wed
=date-CHOOSE(WEEKDAY(date,14),0,1,2,3,4,5,6) // prev Thu
=date-CHOOSE(WEEKDAY(date,15),0,1,2,3,4,5,6) // prev Fri
=date-CHOOSE(WEEKDAY(date,16),0,1,2,3,4,5,6) // prev Sat

Again, the behavior of these formulas is the same as the original formula above except the rollback does not occur if the given date is already the target day of week.

Custom number formatting

All dates in the example shown use the following custom number format :

ddd d-mmm-yy

This number format displays a weekday abbreviation plus a date to make it easier to check results at a glance. The underlying date is unchanged.