How to Populate Monthly Amounts by Date Range in Excel
Question details
The user needs a formula to automatically allocate invoice amounts into specific month columns based on a start date and duration, exclude holiday months, and calculate any unallocated remainder.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a financial ledger or accounts receivable tracker where invoice values must be automatically distributed across multiple month columns according to specific terms.
- Observed behavior
- The user is looking for the correct logical and date formulas to ensure amounts only appear under valid month headers, without requiring manual data entry for each cell.
Ensure that your month column headers (e.g., 'Apr-23') and the start date are formatted as actual date values, rather than plain text, so that date comparison functions work correctly.
Use IF, AND, and EDATE Formulas for Allocation
This method uses logical functions alongside EDATE to check if a month header falls within the active invoice period and automatically distributes the amount.
By comparing the date of each column header to the start date and the end date (calculated using EDATE), Excel can automatically allocate the monthly invoice portion only to the relevant columns.
Ensure the headers in row 1 (e.g., Apr-23, May-23) represent the 1st of each month and are formatted as standard dates.
In the first month cell for your data row, enter a formula similar to: =IF(AND(I$1>=START_MONTH, I$1<EDATE(START_MONTH, DURATION_IN_MONTHS)), INVOICE_AMOUNT/DURATION_IN_MONTHS, ""). Replace the placeholders with your actual cell references.
Select the cell containing the formula, click and hold the fill handle in the bottom-right corner, and drag it to the right across all of your month columns.
Create a 'Remainder' column at the end of the table. Use the formula =INVOICE_AMOUNT-SUM(monthly_amount_cells) to automatically calculate any balance remaining outside the displayed period.
Use a Vertical Three-Column Ledger Alternative
Instead of expanding data horizontally across multiple month columns, utilizing a vertical ledger approach makes data tracking and future analysis significantly easier.
Use WPS Spreadsheet to Handle Complex Date Formulas
WPS Spreadsheet fully supports advanced logical and date functions, including EDATE, AND, and DATEVALUE. Managing complex financial trackers and monthly allocations is fast and entirely compatible with standard spreadsheet formats.
- 1. Download WPS Office: Install WPS Office for free from the official website.
- 2. Open Your Tracker: Launch WPS Spreadsheet and open your existing accounts receivable workbook.
- 3. Apply the Allocation Formula: Input your IF and EDATE formula directly into the cells; WPS processes it exactly like Excel.
- 4. Drag to Auto-Fill: Use the built-in fill handle to quickly drag and copy the formula across all the necessary date columns.
Frequently Asked Questions
Why is the EDATE function returning an error or incorrect value?
This commonly happens if the start date or the month headers are stored as text instead of valid Excel dates. You can use the DATEVALUE function to convert text strings into readable dates for the formula.
How can I format headers so the formula recognizes them as dates?
Enter the 1st of the month (e.g., 01/04/2023) into the cell, then go to Format Cells > Custom and type 'mmm-yy' to display it as 'Apr-23'. The underlying value will remain a valid date.
What is the best way to handle holiday exclusions in monthly allocations?
Handling holidays is more complex and typically requires a helper table listing the exact holiday dates. You would then use functions like WORKDAY or NETWORKDAYS to calculate the duration, skipping the listed dates.
Can I use a PivotTable to summarize data formatted in month columns?
While possible, it is difficult. PivotTables work best with vertical, normalized data. If you need robust reporting, it is highly recommended to unpivot your columns into a traditional Date/Debt/Credit ledger format.
