4 Easy Ways to Calculate Percentage of Time in Excel

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.

Methods to calculate the percentage of time


📁 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.

Dataset for calculating the percentage of time

method

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

Using a simple formula

Applying the fill handle tool

  • Finally, we get the result in column F.

The output of the percentage of time

📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends

method

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

Writing a formula for duration

  • 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.

Using the Iferror function

  • Now we use the Fill Handle tool to copy our formula.
  • Finally, we get the result in column H.

The output of the iferror function to calculate the percentage of time

📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel

method

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.

Using the Yearfrac function

  • 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

calculating the percentage

  • After that, we use the Fill handle tool again to copy the formula.
  • Finally, we get the result in column I.

The output of the Yearfrac function to calculate the percentage of time

📕 Read More: How to Calculate Turnaround Time in Excel Excluding Weekends

method

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.

Calculating the days passed

  • Next, we copy the formula in column H.
  • After that, we write this formula in the cell I6 formula box.

=H6/G6

Calculating the percentage of time by dividing

  • Now we use the Fill handle tool to copy the formula.
  • Finally, we get our result in column I.

The output of the Yearfrac, Date & year functions to calculate the percentage of time


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.

Using the Datedif function

  • Now we use the Fill Handle tool to drag the formula.
  • Finally, we get our result in column G.

The output of the Datedif function to calculate the percentage


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)

Using the Sum function

  • Now we go to cell E6 and type this formula.

=D6/$D$12

Applying a division formula

  • After that, we use the Fill Handle tool to drag the formula.
  • Finally, we get our result in column E.

Output as the percentage of time on a task

📕 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.

Getting the Value error

  • So to avoid this error we rewrite the date in column E as mm/dd/yyyy format.

Changing the date format

  • After that, we write the formula again and press the Enter button.
  • Finally, we get our result in column F.

The output of the difference in time


📄 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

(Visited 53 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo