Have you ever wanted to track what percent of the month is completed? In this article, we are going to show you how to calculate the percent of completed month in Excel.
If you want to calculate the percent of completed year instead, follow the link: How to calculate the percent of completed year
Formula
* CurrentDate represents the date value or reference for the date you want to calculate.
How It Works
To calculate the percent of completed month, you only need 2 Excel functions:
The math is simple. You need the divide the day of the date you want to calculate by the number days in the month of the specified date. It is a simple proportion calculation.
Finding the day of a month is simple. Excel’s DAY function does the job easily. Just make sure that you supply a valid date. String dates like “6/27/2024” are not valid. Because Excel keeps the dates as numeric values.
For the other part of the proportion, you need to find the last day of the month by the specified date. Due to various day count of months, you need to calculate it dynamically. The EOMONTH function comes in handy for this situation. The function returns the serial date number of the last day of the month, for a certain number of months before or after a given date.
The EOMONTH function has 2 arguments: start_date and months. If you supply 0 for the months argument, the function gives you the date of the last day of the month. For example:
The DAY function can easily parse the day of the date returned from the EOMONTH function. Thus, the final formula for the date in the cell B5 will be:
DATE Function Alternative to calculate the percent of completed month
You can also calculate the percent of completed month with DATE, YEAR and MONTH functions instead of the EOMONTH. Obviously, the formula will be longer with 2 more functions.
The DATE function returns a date serial number by given year, month and day numbers. You can find the last day of the month by supplying next month’s number and setting day as 0 (zero). Because there isn’t a day-0, Excel will round the date down to the last day of the previous month. Here is the formula:
