Basic Formula for Percentage Calculation
=(Part/Total)*100Excel automatically multiplies the result of the ‘=Part/Total’ portion with 100 when you apply the percent (%) Number format.
The dataset below showcases a percentage of working hours.
=(8/24)
Example 1 – Calculating the Percentage of Time Using a Simple Formula
This a sample dataset.
Steps:
- Enter the following formula in C8 and press Enter.
=C5/C4
- 25% of your vacation is spent at home.
- 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
Example 2 – Using the Excel DATEDIF Function to Calculate the Percentage of Time in a given period
Use the DATEDIF function and the TODAY function to calculate how much time has elapsed between a date range and the current date. Use the IFERROR function to avoid errors.
Steps:
- Enter the formula in F5.
=IFERROR((DATEDIF(C5,TODAY(),"d"))/E5,"")
- Press Enter to see the result.
- Use the Fill Handle (+) to copy the formula to the rest of the cells.
- The percentages of time completed will be displayed. In Task 3, the formula returns blank, as the task has not started.
Formula Breakdown
TODAY()
returns the current date {44623}.
➤ DATEDIF(C5,TODAY(),”d”))
returns {92}.
➤ IFERROR((DATEDIF(C5,TODAY(),”d”))/E5,””)
returns {.239583333333333}; which is multiplied by 100 due to Number formatting and converted to 24%.
Read More: How to Calculate Lag Time in Excel
Example 3 – Applying the YEARFRAC Function to Find the Percentage of a date range in a Year in Excel
Steps:
- Enter the formula in D5 and press Enter.
=YEARFRAC(B5,C5,1)
- This is the output.
- Copy the formula to the rest of the cells using the Fill Handle.
Read More: How to Calculate Lead Time in Excel
Example 4 – Combining the YEARFRAC, DATE, and YEAR Functions to Get the Percentage of time in a Year
Use a combination of the YEARFRAC, DATE, and YEAR functions.
Steps:
- Enter the formula in C5. Press Enter.
=YEARFRAC(DATE(YEAR(B5),1,1),B5)
- This is the output.
- Use the Fill Handle to copy the formula to the rest of the cells.
- To see the percentage of remaining time, subtract the above formula from 1.
=1-YEARFRAC(DATE(YEAR(B5),1,1),B5)
- Press Enter and use the Fill Handle to see the percentage of days in a year.
Formula Breakdown
➤ YEAR(B5)
converts the date in B5 to 44576 and returns the year {2022}.
➤ DATE(YEAR(B5),1,1)
returns {44562}.
➤ YEARFRAC(DATE(YEAR(B5),1,1),B5)
returns {4%}.
Read More: How to Calculate Median Follow-up Time in Excel
Things to Remember
To format the output column: Home > Number group. Click ‘%’.
Use Ctrl + Shift + % to change the number format.
Download Practice Workbook
Download the practice workbook.
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!
