In this tutorial, I will show you how to calculate the percentage of time in Excel using the basic percent formula. Besides, I will show some examples of using Excel functions while calculating the percentage of the time. So, let’s explore the examples.
Basic Formula for Percentage Calculation
We all know the basic percentage formula as below:
=(Part/Total)*100In Excel, you won’t have to multiply ‘Part/Total‘ with 100 as stated above. This is because Excel automatically multiplies the result of the ‘=Part/Total’ portion with 100 when you apply the percent (%) Number format.
To illustrate, if an employee works 8 hours in a day (24 hours), then the percentage of time the employee has worked in a day will be as follows:
=(8/24)
Now, you might wonder whether the time format has any effect on the above calculation in Excel or not. Luckily, Excel stores time as a number, it displays a different number of time formats depending on our preference. For instance, in the above example, I have used the time format: hh:mm:ss. However, using that time format did not affect the percentage calculation. So, whatever the time format is, simply use the ‘=Part/Total‘ formula to calculate the percentage of the time.
How to Calculate Percentage of Time in Excel: 4 Examples
1. Calculate the Percentage of Time Using a Simple Formula
First of all, I will show you an example of calculating the percentage of time using the basic percentage formula. Suppose, you have a 20-day vacation. Now you spent 5 days at home and then went to New York to spend the rest of the 15 days. Now, if you want to know what percent of your vacation you have spent by staying at home and in New York, then follow the below steps.
Steps:
- Type the following formula in Cell C8 and press Enter from the keyboard.
=C5/C4
- As a result, you will find that 25% of your vacation is spent at home.
- Similarly, you can use the below formula to get the percentage of vacation days spent in New York.
=C6/C4
Read More:Â How to Calculate Total Hours in Excel
2. Excel DATEDIF Function to Calculate Percentage of Time Completed
This time, I will use the DATEDIF function along with the TODAY function to calculate how much time has elapsed between a date range and the current date. While doing this calculation, I will use the IFERROR function to get rid of the error, if any of the tasks is not started yet. For instance, I have a list of tasks, mentioning their start date and end date along with the duration of the tasks. So, now I will calculate how much of this duration is completed to date.
Steps:
- Type the below formula in Cell F5.
=IFERROR((DATEDIF(C5,TODAY(),"d"))/E5,"")
- Hit Enter and we will get the below result. Now use the Fill Handle (+) tool to copy the formula to the rest of the cells.
- As a result, we will receive percentages of time completed for all of the tasks. For Task 3, the formula returns blank as the task is not started to date.
🔎 How Does the Formula Work?
➤ TODAY()
This part of the formula returns the current date which is {44623}.
➤ DATEDIF(C5,TODAY(),”d”))
Then this part of the formula returns {92}.
➤ IFERROR((DATEDIF(C5,TODAY(),”d”))/E5,””)
Finally, this part of the formula returns {.239583333333333}; which is multiplied by 100 due to Number formatting and finally converted to 24%.
Read More: How to Calculate Lag Time in Excel
3. Apply the YEARFRAC Function to Find the Percentage of a Year in Excel
Now, I will calculate the percentage completed in a year between specific dates. To do that, I will use the YEARFRAC function in Excel. For example, I have a list of date ranges containing start and end dates. Let’s calculate the day’s percentage completed for the year 2022 between the below dates.
Steps:
- First, type the below formula in Cell D5 and press Enter.
=YEARFRAC(B5,C5,1)
- Then you will get the below result. Copy the formula to the rest of the cells using the Fill Handle tool in excel.
Read More: How to Calculate Lead Time in Excel
4. Combine YEARFRAC, DATE, and YEAR Functions to Get the Percentage of Year Complete
This time, I will find what percentage of days has been completed in a year using the combination of YEARFRAC, DATE, and YEAR functions. Let’s have a look at the steps involved in this process.
Steps:
- Type the below formula in Cell C5. Then hit Enter.
=YEARFRAC(DATE(YEAR(B5),1,1),B5)
- Consequently, we will get the below result. Use the Fill Handle tool to copy the formula to the rest of the cells.
- Now, if you want to get the percentage of the year remaining, just subtract the above formula from 1 as below.
=1-YEARFRAC(DATE(YEAR(B5),1,1),B5)
- Hit Enter and use the Fill Handle tool to get the percentage of the days of the year as follows.
🔎 How Does the Formula Work?
➤ YEAR(B5)
This part of the formula converts the date of Cell B5 to 44576 and returns the year of the date which is {2022}.
➤ DATE(YEAR(B5),1,1)
Later, this part of the formula returns {44562}.
➤ YEARFRAC(DATE(YEAR(B5),1,1),B5)
Finally, this part of the formula returns {4%}.
Read More: How to Calculate Median Follow-up Time in Excel
Things to Remember
➨ Be careful with the cell’s number format while doing percentage calculations. You can format the output column by following the path: Home > Number group. Then click on the ‘%’ symbol (see screenshot).
➨ You can use the Keyboard shortcuts (Ctrl + Shift + %) to change the output columns number format.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss several examples to calculate the percentage of time in Excel elaborately. Hopefully, these examples and explanations will be enough to solve your problems. Please let me know if you have any queries.
Related Articles
- How to Calculate Total Time in Excel
- How to Calculate the Duration of Time in Excel
- How to Sum Time in Excel
- [Fixed!] SUM Not Working with Time Values in Excel
<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
