Mahbubur Rahman
Assume you have a set of dates in a spreadsheet and want to know what percentage of the year or month has passed or remains based on the given date. In this article, we will guide you through several methods to Calculate Percentage of Month in Excel. For your better understanding, we will use a sample dataset containing Dates.
Download Practice Workbook
4 Ways to Calculate Percentage of Month in Excel
In this blog post, we will see the use of DAY, EOMONTH, NETWORKDAYS, and YEARFRAC functions and also use a general mathematical equation for our calculation.
Method 1: Calculate Percentage of Month by DAY Function
The easiest method to calculate the percentage of the month is the combination of the DAY function and EOMONTH function. Suppose, we have a list of dates like the dataset below and we want to see the known percentage of the month and how much is remaining.
Steps:
- First, type the following formula in cell D5.
=DAY(B5)/DAY(EOMONTH(B5,0))
- Now, press the ENTER key, and to change the number formatting to percentage click the % sign in the Number tab.
- As a result, we got the result as follows.
Here, EOMONTH(B5,0) returns the total number of days in a month as 44592 which is further converted by the DAY function like 31, and DAY(B5) returns us the number of days in a month which is 15 in this case.
- After that, type the following formula in cell E5.
=1-C5
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
That’s it. So, we can say that on the day 15 of January 48% of the total month is completed and remains at 52%.
Read More: How to Calculate Monthly Growth Rate in Excel (2 Methods)
Method 2: Calculate Percentage Using YEARFRAC Function
Now, we will see another useful function, the YEARFRAC function to calculate. In this scenario, we have got different dates from different months, we will see how much of the month is completed of the year on that specific date.
Steps:
- First, type the following formula in cell D5.
=YEARFRAC(DATE(YEAR(B5),1,1),B5)
- At this point, press the ENTER key, and to change the number formatting to percentage click the % sign in the Number tab.
- As a result, we got the result as follows.
Here, DATE(YEAR(B5),1,1) returns the total number of days in a month as 44562 which is further converted by the =YEARFRAC(44562, B5) formula like 0.038.
- Now, type the following formula in cell E5.
=1-C5
- Finally, press the ENTER key and drag it down to AutoFill rest of the series.
That’s it. So, we can say that in day 15 of January 3.9% of the total Year is completed and remains 96.1%.
Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
Similar Readings
- How to Calculate Reverse Percentage in Excel (4 Easy Examples)
- Calculate Bacterial Growth Rate in Excel (2 Easy Ways)
- How to Calculate Net Profit Margin Percentage in Excel
- Calculate Percentage Based on Conditional Formatting (6 Ways)
- How to Use the Exponential Growth Formula in Excel (2 Methods)
Method 3: Calculate Percentage of Month with General Equation
Now, we will use a simple equation to determine the percentage complete in each phase.
Steps:
- First, type the following formula in cell E5.
=(D6-C6)/($D$5-$C$5)
We will use absolute reference here, as our start date and end date are fixed.
- Now, press the ENTER key.
- At last, drag down to AutoFill rest of the series.
- In order to check, if the calculation is correct, type the following formula in cell E12.
=SUM(E6:E10)
- Finally, press the ENTER key.
Read More: Excel formula to calculate percentage of grand total (4 Easy Ways)
Method 4: Use NETWORKDAYS Function to Calculate Percentage of Month
If we want to calculate only the working days which are from Monday to Friday, we can use the NETWORKDAYS function. We have a dataset containing dates of the month, we will now see how much is completed in each phase cumulatively.
Steps:
- First, type the following formula in cell E5.
=NETWORKDAYS(C6,D6)/NETWORKDAYS($C$5,$D$5)
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
Read More: Percentage Formula in Excel (6 Examples)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
That’s all for the article. These are 4 different methods to calculate percentage of month in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- How to Calculate Percentage of Sales in Excel (5 Suitable Methods)
- Calculate Percentage Increase between 3 Numbers in Excel
- Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)
- How to Calculate Grade Percentage in Excel (3 Easy Ways)
- Subtract a Percentage in Excel (Easy Way)
- Percentage Difference between Two Percentages Excel (2 Easy Ways)
