Calculate the Percentage of a Month Covered by a Date Range in Excel
Question details
Calculate the percentage of a calendar month covered by an inclusive start and end date.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The start date is July 2, 2020, in A3, and the end date is July 16, 2020, in B3. The goal is to calculate the covered portion of July in C3.
- Observed behavior
- The inclusive range covers 15 of July's 31 days, so the expected result is approximately 48.39%.
Enter actual date values in A3 and B3, rather than text, and confirm that B3 is on or after A3. The basic formulas below assume both dates belong to the same calendar month.
Calculate July coverage with a fixed 31-day denominator
Use this formula for the given July example or another month known to have 31 days.
Select A3 and enter July 2, 2020. Select B3 and enter July 16, 2020. If Excel does not recognize the entries as dates, use =DATE(2020,7,2) and =DATE(2020,7,16), respectively.
Select C3, type =(B3-A3+1)/31, and press Enter. Adding 1 includes both the start and end dates, giving 15 covered days.
Select C3, open Home > Number, and choose Percentage. Use Increase Decimal or Decrease Decimal to display two decimal places.
Automatically use the number of days in the month
Use EOMONTH to handle 28-, 29-, 30-, and 31-day months without manually changing the denominator.
Calculate Monthly Date Coverage in WPS Spreadsheet
Use WPS Spreadsheet to subtract dates, include both endpoints, and divide by the month's day count. Apply percentage formatting to show the same monthly coverage calculation used in Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open your Excel workbook, or create a blank spreadsheet.
- 2. Enter the dates: Select A3 and enter =DATE(2020,7,2), then select B3 and enter =DATE(2020,7,16).
- 3. Calculate monthly coverage: Select C3, enter =(B3-A3+1)/DAY(EOMONTH(A3,0)), and press Enter.
- 4. Apply percentage formatting: Select C3 and use Home > Number Format > Percentage. Adjust the decimal places to two to display 48.39%.
Frequently Asked Questions
Why does the formula add 1 to the date difference?
Subtracting A3 from B3 counts the elapsed days between the dates, not both endpoints. For July 2 through July 16, =B3-A3 returns 14, while =B3-A3+1 returns the inclusive count of 15 days.
How do I calculate coverage for February or a 30-day month?
Enter =(B3-A3+1)/DAY(EOMONTH(A3,0)) in C3 when both dates are in the same month. EOMONTH finds the month's last date, and DAY returns its day count, including 29 days for February in a leap year.
Why does Excel show 0.483870968 instead of 48.39%?
The formula returns a fraction of the month. Select the result cell, choose Home > Number > Percentage, and adjust the display to two decimal places. Do not multiply the formula by 100 when using Percentage formatting.
What if my date range spans two months?
Put any date from the target month in D3, then enter =MAX(0,MIN(B3,EOMONTH(D3,0))-MAX(A3,EOMONTH(D3,-1)+1)+1)/DAY(EOMONTH(D3,0)) in C3. Format C3 as a percentage. This counts only the days overlapping the target month; repeat with a different date in D3 for another month.
Why does my date calculation return #VALUE!?
One or both date cells may contain text instead of Excel date values. Replace A3 and B3 with =DATE(2020,7,2) and =DATE(2020,7,16) to test the example, then re-enter the percentage formula in C3. Changing a text cell's display format alone does not convert it to a date.
