How to Calculate Percentage of Time in Excel (4 Suitable Examples)

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.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Basic Formula for Percentage Calculation

We all know the basic percentage formula as below:

=(Part/Total)*100

In 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)

Basic Formula for Percentage Calculation

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.


4 Examples to Calculate Percentage of Time in Excel

1. Calculate Percentage of Time Using 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 New York, then follow the below steps.

Calculate Percentage of Time Using Simple Formula

Steps:

  • Type the following formula in Cell C8 and press Enter from the keyboard.
=C5/C4

Calculate Percentage of Time Using Simple Formula

  • As a result, you will find that 25% of your vacation is spent at home.

Calculate Percentage of Time Using Simple Formula

  • Similarly, you can use the below formula to get the percentage of vacation days spent in New York.
=C6/C4

Read More: Timesheet Formula in Excel (5 Examples)


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 till 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,"")

Excel DATEDIF Function to Calculate Percentage of Time Completed

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

Excel DATEDIF Function to Calculate Percentage of Time Completed

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

Related Content: How to Subtract Minutes from Time in Excel (7 Methods)


Similar Readings:


3. Apply YEARFRAC Function to Find Percentage of Year in Excel

Now, I will calculate the percentage complete of 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)

Apply YEARFRAC Function to Find Percentage of Year in Excel

  • 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 the Duration of Time in Excel (7 Methods)


4. Combine YEARFRAC, DATE, YEAR Functions to Get 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)

Combine YEARFRAC, DATE, YEAR Functions to Get Percentage of Year Complete

  • Consequently, we will get the below result. Use the Fill Handle tool to copy the formula to the rest of the cells.

Combine YEARFRAC, DATE, YEAR Functions to Get Percentage of Year Complete

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

Combine YEARFRAC, DATE, YEAR Functions to Get Percentage of Year Complete

  • 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 Time in Excel (16 Possible Ways)


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

Things to Remember

➨ You can use the Keyboard shortcuts (Ctrl + Shift + %) to change the output columns number format.


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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo