Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Get percent of year complete

Read time: 25 minutes

In this tutorial, you will learn how to get percent of year complete for a date. This is possible by using a combination of Excel functions YEAR, DATE and YEARFRAC. YEAR function returns a year for a date. DATE function returns a date for a day, month and year. YEARFRAC function returns a percentage of the year completed for a date.

Get percent of year complete

Formula

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

Explanation

The YEAR function returns a year for a selected date.

The DATE function returns a date for the selected day, month and year. In our case, YEAR(date) will be a year parameter for the function. The day and month parameters will be 1, as we want to calculate the percentage from the start of a year (1-Jan).

The YEARFRAC function returns a percentage of a year passed. The parameters of the function are Start_date – which is the start of the period for which we want to calculate a percentage. In our case, that is the result of DATE function (1-Jan of dates year); date, which is selected date.

Example 1

In the following example, we will see how to get a percent of the year complete for a date. Let’s see how the formula looks like:

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

 

Figure 1. Get percent of year complete

 

As you can see in the picture, in column B (“Date”), we have dates for which we want to calculate the percent of year complete. In the column C (“Percent of year complete”) we want to get the percentage of the year complete.

In the example, we want to get the project end date based on cells B3 and put it in the cell C3. First, function YEAR(B3) will return 2018, which is the year for date 25-Jan-18. This year will be the parameter for DATE function (2018, 1, 1), which will, therefore, return 1-Jan-18. This date will be start date parameter for YEARFRAC (1-Jan-18, 25-Jan-18). The result in C3 is 7%, which is the percentage of the completed year on date 25-Jan-18.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have multiple columns on my spread sheet and want to switch from A-Z to Z-A based on values in column A
Solved by V. H. in 38 mins
Concat formula is incorrectly formatting the date in column C. Currently column B has dd/mm/yyyy and I require mm/dd/yyyy
Solved by Z. Y. in 31 mins
I need a formula that will allow me to calc before a certain date and after a certain date. =if(A2<=DATE(2015,8,27),(F2*1.14),(F2*1.15)) It just multiplies column F with 1.15. I'm assuming I need to bring in the date column into the equation?
Solved by M. Q. in 20 mins
I need assistance setting up a time sheet formula where I can create a column of contiguous dates down the left hand side of the spread sheet. So I can copy a contiguous column of dates, rather than entering each date manually. I would only need to have work days in the list, ie Monday to Friday inc. with a couple of spare lines between each week.
Solved by Z. Y. in 40 mins
Example: If a loan closes between October 1st & 15th, the first mortgage payment is due November 1st. If it closes between October 16th & 31st, the first payment is due December 1st.
Solved by S. S. in 15 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc