How to Calculate Percentage of Month in Excel (4 Easy 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.

How to Calculate Percentage of Month in Excel: 4 Easy Ways

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 for the 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%.

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 on day 15 of January 3.9% of the total Year is completed and remains 96.1%.

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 for the 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.

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 for the rest of the series.

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.

Download Practice Workbook

Percentage of Month.xlsx

Conclusion

That’s all for the article. These are 4 different methods to calculate the percentage of the 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.