Md. Ahaduzzaman
Every once in a while we may need to know how much time is spent on our different everyday tasks so that we can make a schedule. It helps us to plan any future task. Also, we can use this record as a reference for any future endeavors. We can very conveniently calculate the percentage of time in Excel. In this article, we will discuss 4 simple but efficient ways to calculate the percentage of time in Excel. We will employ different functions like DATEIF, YEARFRAC, YEARFRAC with DATE & YEAR together, etc. We will also discuss how we can do that without using any function and using only simple formulas. You can take an overview of different methods and their corresponding outcomes in this article from the snapshot given below.
📁 Download Excel File
Download the practice file from here.
Basics of Percentage Calculation
In mathematics, a percentage is a number that is expressed as a fraction of 100. If we want to find the percentage of a number, we must divide it by 100 and then multiply the result by the original amount. Therefore, the proportion refers to a component per 100. Per 100 is what the word percent means. It is represented by the symbol “%.”.
Formula
Formula for Percentage = (Individual value/Sum of the values) × 100
Example: 4/5 × 100 = 0.8 × 100 = 80%
Learn to Calculate Percentage of Time in Excel with These 4 Methods
In this article, we will learn 4 different ways to calculate the percentage of time in Excel. Let’s assume that we have the record of the Start Date & End Date of the employee’s tasks. At some fixed date we want to know how much progression each employee has made with their tasks. In the following sections, we will learn about that. The sample dataset for our record is as follows for demonstration purposes.
1. Utilizing Simple Formula
In this section, we will create our very own simple formula to calculate the percentage of time in Excel. We will basically use the formula as shown in the previous section. This is the simplest of all methods because we don’t use any function here. So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell F6 formula box to write this formula.
=E6/D6
- Now we use the Fill Handle tool to copy the formula.
- Finally, we get the result in column F.
📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
2. Using IFERROR Function
In this section, we will use the IFERROR function to calculate the percentage of time in Excel. This function checks for an error in the argument. If there is any error we can set up an error message beforehand. So This method is very useful to check if there is any error in the argument or not.
Overview of IFERROR Function
The syntax for this particular function is IFERROR(value, value_if_error). For instance, we may use the IFERROR function to return a default value rather than an error if cell B1 contains a formula that could result in an error.
=IFERROR(B1, “Not Available”)
If there is no error, this formula will provide the value of the formula in cell B1. The string “Not Available” will be the return if there is a problem.
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell F6 formula box and type this formula to know the Duration.
=E6-D6+1
- Now we copy the formula first in other cells in column F.
- After that, we write this formula in the cell H6 formula box.
=IFERROR((DATEDIF(D6,G6,"d"))/F6,"")
🔨 Formula Breakdown
👉 Here (DATEDIF(D6,G6,”d”)) gives us the difference between the two dates in the cell.
👉 IFERROR((DATEDIF(D6,G6,”d”))/F6,””) divides the difference by cell F6 and returns us the value.
👉 But if there is any error the return will be blank.
- Now we use the Fill Handle tool to copy our formula.
- Finally, we get the result in column H.
📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel
3. Using YEARFRAC Function
In this section, we will apply the YEARFRAC function to calculate the percentage of time in Excel. This function returns us the fraction of the year as days between two dates. This function is very useful when we want to know how many days in the year are over and how much is remaining.
Overview of YEARFRAC Function
The syntax for this function is YEARFRAC(start_date, end_date, [basis]). The YEARFRAC function, for instance, is very handy to determine the percentage of the year between two dates if the start date is in cell B1 and the end date is in cell D1.
=YEARFRAC(B1, D1)
The result of this formula, rounding to the nearest four decimal places, will be the percentage of the year that passed between the two dates.
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell H6 formula box to write this formula.
=YEARFRAC(D6,F6,1)*365
- Here we get the return as the days have passed already.
- Now we copy the formula in other cells in column H.
- Following that, we write this formula in the cell I6 formula box.
=H6/G6
- After that, we use the Fill handle tool again to copy the formula.
- Finally, we get the result in column I.
📕 Read More: How to Calculate Turnaround Time in Excel Excluding Weekends
4. Implying YEARFRAC, DATE & YEAR Functions Together
In this section, we will apply the YEARFRAC, DATE & YEAR functions together to calculate the percentage of time in Excel. They return the date & year corresponding to the arguments. This method is almost the same as the previous method but the main difference is we provide the arguments as a function here.
Overview of DATE Function
The syntax of the DATE function is DATE(year,month,day). The DATE function is useful to construct a date. For instance, if we have the year value in cell B1, the month entry in cell C1, & the day entry in cell D1.
=DATE(B1, C1, D1)
The date that corresponds to the values for the year, month, and day in cells B1, C1, and D1, respectively, will be returned by this formula.
Overview of YEAR Function
The syntax of the YEAR function is YEAR(date). The YEAR function is useful to get the year from a date in cell B1 as follows.
=YEAR(B1)
The year of the date in cell B1 will be the result of this formula. For instance, the formula will return the value “2022” if the date in cell B1 is “3/4/2022”.
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell H6 formula box and write this formula.
=YEARFRAC(DATE(YEAR(D6),1,1),E6)*365
🔨 Formula Breakdown
👉 YEAR(D6) returns us the year as a result.
👉 We get the particular date as a return from DATE(YEAR(D6),1,1) function.
👉 YEARFRAC(DATE(YEAR(D6),1,1),E6)*365 returns us the days that have passed already.
- Next, we copy the formula in column H.
- After that, we write this formula in the cell I6 formula box.
=H6/G6
- Now we use the Fill handle tool to copy the formula.
- Finally, we get our result in column I.
How to Calculate Percentage Between Two Dates in Excel
In this section, we will use the DATEDIF function to calculate the percentage between two dates in Excel. This function returns us the difference between 2 dates.
Overview of DATEDIF Function
The syntax of this function is DATEDIF(start_date,end_date,unit). The DATEDIF function can be used to determine the number of days between two dates, for instance, if the start date is in cell B1 and the end date is in cell C1.
=DATEDIF(B1, C1, “d”)
So the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the cell G6 formula box.
=(DATEDIF(D6,F6,"d")+1)/(DATEDIF(D6,E6,"d")+1)
- Then here we divide the 2 values one by the other to get the percentage.
- Now we use the Fill Handle tool to drag the formula.
- Finally, we get our result in column G.
How to Calculate Percentage of Time Spent on a Task
In this section, we will learn how to calculate the percentage of time spent on a task using the SUM function. The syntax of this function is SUM(number1,[number2],…). It returns us the summation of one or more number ranges. The steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell D12 formula box and type this formula.
=SUM(D6:D11)
- Now we go to cell E6 and type this formula.
=D6/$D$12
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get our result in column E.
📕 Read More: 5 Easy Ways to Fix When SUM Time Is Not Working in Excel
VALUE Error (#VALUE!) When Subtracting Time in Excel
In this section, we will learn about an error message that is quite common when we use Excel. It is called Value Error (#VALUE!). We will discuss how they can happen when we subtract time in Excel and will also discuss how to avoid it. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Here we face the Value Error (#VALUE!) in column F.
- The main reason behind this is that the date format entry in column E is not right.
- So to avoid this error we rewrite the date in column E as mm/dd/yyyy format.
- After that, we write the formula again and press the Enter button.
- Finally, we get our result in column F.
📄 Important Notes
🖊️ When we use any Excel function we need to be careful about the function arguments.
🖊️ Also not maintaining consistency in data types may cause errors.
📝 Takeaways from This Article
📌 We can prepare any simple formula by ourselves to calculate the percentage of time in Excel.
📌 We can utilize the IFERROR function to calculate the percentage of time in Excel.
📌 Also we can use the YEARFRAC function to do the same job.
📌 Apart from these we can also use the YEARFRAC, DATE & YEAR functions together to do this task.
Conclusion
In this article, we have learned 4 simple ways to calculate the percentage of time in Excel. We can use different Excel functions such as DATEIF, YEARFRAC, YEARFRAC with DATE & YEAR together, etc. We can also use simple formulas to do the same task. Actually, It is totally up to the users which methods they want to follow. Furthermore, if you have any questions regarding this article you can comment in our comment section. If you are looking for more Excel-related topics and solve processes you can visit our website www.Excelden.com.
Related Articles
- How to Calculate Average Response Time in Excel
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- 2 Ways to Calculate Travel Time Between Two Cities in Excel
- How to Calculate Overtime and Double Time with Formula in Excel
