How to Calculate Monthly Values Based on Start and End Dates in WPS Office Spreadsheets
Question details
The user needs to create a month-by-month spreadsheet summary that allocates annual values across months based on start dates, end dates, prorated partial periods, and future-year projections.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has records with start dates, end dates, and annual values and wants to allocate those values to the correct months and years, including future-year projections.
- Observed behavior
- The user wants to produce a month-by-month prorated summary from date ranges and annual values instead of having only a single total per record.
Confirm whether the values are balances, payments, withdrawals, or another measure, and identify the original non-prorated annual value for each row. Verify that all Start Date and End Date cells are recognized as real dates in WPS Office Spreadsheets.
Build a Month-by-Month Allocation Table with Date Overlap Formulas in WPS Office Spreadsheets
Use this method when you need to allocate annual values to individual months based on each record's start and end dates, including months that contain only a partial period.
WPS Office Spreadsheets supports the same date arithmetic and functions as Excel, so you can calculate the overlap between a record's date range and each calendar month. The result is the number of days in that row that belong to the month; multiply that by a daily rate to get the prorated monthly value.
Set up the model so the month headers are real dates formatted as mmm-yyyy. This lets you use EDATE and MIN/MAX formulas instead of manually checking each cell.
Open WPS Office Spreadsheets, create a new worksheet, and enter your records with separate columns for Start Date, End Date, and Annual Value. In a header row, enter the first day of each month with =DATE(2024,1,1) and =EDATE(previous_month,1) across the columns.
In the first allocation cell, enter =MAX(0, MIN($C2, EDATE(D$1,1)-1) - MAX($B2, D$1) + 1). Replace $C2 with the End Date cell, $B2 with the Start Date cell, and D$1 with the month header cell, then copy the formula across and down.
Modify the formula to multiply the overlap by the appropriate rate. For an annual value prorated daily, use =MAX(0, MIN($C2, EDATE(D$1,1)-1) - MAX($B2, D$1) + 1)*($E2/365). For a total value spread across the stated period, divide $E2 by the full period days instead of 365.
Continue the month header series with EDATE for future years. Apply the same formulas to those columns so projected monthly values are calculated automatically from the start and end dates.
Summarize the Allocation Results by Month and Year with SUMIFS
Use this after building an allocation table when you want a clean monthly summary sheet instead of a full date-range grid.
Use a Simple Monthly Rate for Full-Year or Future-Year Records
Use this when records cover entire years or future-year amounts do not need daily proration and can be spread evenly by month.
Create a Prorated Monthly Summary in WPS Office Spreadsheets
WPS Office Spreadsheets can directly create a month-by-month summary from start and end dates using built-in date functions, proration formulas, and SUMIFS or PivotTable reporting. It handles .xlsx workbooks and supports the same calculation logic you already use in Excel.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS site, install it, and open WPS Office Spreadsheets.
- 2. Prepare the workbook data: Create a new workbook and enter your records with Start Date, End Date, and Annual Value columns, then add month headers using =DATE and =EDATE.
- 3. Enter the proration formula: In the first allocation cell, enter =MAX(0, MIN($C2, EDATE(D$1,1)-1) - MAX($B2, D$1) + 1)*($E2/365) and copy it across the monthly grid.
- 4. Summarize and review: Use SUMIFS or Insert > PivotTable to create a clean month-by-month summary, then check partial first and last months before distributing or reporting the results.
Frequently Asked Questions
How do I calculate the overlapping days between a date range and a month in WPS Office Spreadsheets?
Use =MAX(0, MIN(end_date, EDATE(month_start,1)-1) - MAX(start_date, month_start) + 1). This returns the number of days from the record's date range that fall in that calendar month.
How can I prorate an annual value by the number of days in a month?
Calculate the monthly overlap days, then multiply by annual_value/365. For example, if a row uses $E2 for the annual value and D$1 for the month header, the formula is =MAX(0, MIN($C2, EDATE(D$1,1)-1) - MAX($B2, D$1) + 1)*($E2/365).
How do I create future-year monthly projections for the same date range?
Continue the month header row with =EDATE(previous_month,1) for all future years required. The same MIN/MAX and rate formula will calculate the projected monthly amounts automatically.
What should I check before building the monthly summary in WPS Office?
Confirm whether the values are balances, payments, withdrawals, or another measure, and identify the original non-prorated annual values. Also verify that the Start Date and End Date cells are real dates rather than text.
Can WPS Office open an Excel workbook with these formulas and keep the calculations?
Yes. WPS Office Spreadsheets supports .xlsx files and Excel date functions such as DATE, EDATE, MIN, MAX, and SUMIFS, so the workbook remains compatible.
