How to Calculate Percentage of Month in Excel (4 Ways)

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.

calculate percentage of month in excel


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.

calculate percentage of month in excel with Day function

Steps:

  • First, type the following formula in cell D5.
=DAY(B5)/DAY(EOMONTH(B5,0))

calculate percentage of month in excel with Day function

  • 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

calculate percentage of month in excel with Day function

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

calculate percentage of month in excel with Day eomonthfunction

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.

calculate percentage of month in excel using YEARFRAC function

Steps:

  • First, type the following formula in cell D5.
=YEARFRAC(DATE(YEAR(B5),1,1),B5)

calculate percentage of month in excel using YEARFRAC function

  • 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


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.

calculate percentage of month in excel

  • At last, drag down to AutoFill rest of the series.

calculate percentage of month in excel generl equation

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

calculate percentage of month in excel using NETWORKDAYS

  • 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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo