Why Excel Counts Your Credit Card Payment Twice Across Month End
You combine your checking and credit card exports to see what you spent in October. Excel shows $1,207 after refunds. There is a $480 autopay on October 31, but the matching card credit does not post until November 2. There is also a separate $480 purchase at a tool supplier. Filtering out every $480 row will make this worse.
The autopay settles the card balance. The supplier purchase belongs in spending. In this synthetic workbook, excluding the confirmed repayment brings October net spending to $727. Keep the original rows for account reconciliation.
Download the practice workbook
Download Jolene_Card_Payment_Practice.xlsx
The workbook contains fictional USD records in Checking and Credit Card, plus a Spending review sheet. October uses posted dates. The November rows are included to help check payments crossing month end. This is a spending classification exercise, not a bank balance reconciliation or a customer outcome.
1. Keep the first few days of the next month
Keep account name, transaction ID, posted date, description, payment reference, transaction type and amount. Standardize signs before combining exports. In this workbook, purchases and money out are negative. Refunds and payment credits are positive.
If you append the exports in Power Query, align their column names first. Microsoft explains how appending queries works. Appending puts the records together; it does not decide which ones are transfers.
2. Match the payment, not just the amount
Checking!B4:F4 records the October 31 autopay of −$480. The matching credit is in ‘Credit Card’!B7:F7 on November 2. Both carry PMT-911, and their transaction types identify a card payment and payment credit. Together, those details support treating the checking debit as a transfer.
The $480 Harbor Tool Supply purchase in ‘Credit Card’!B3:F3 stays in spending. Its amount happens to match the autopay. That does not make it a duplicate.
If your files omit the payment reference, check the destination card account and issuer payment details. Leave an uncertain pair open for review rather than classifying it from equal amounts alone.
3. Calculate October spending
| Calculation | USD | Workbook cells |
|---|---|---|
| Checking purchases: $64 + $82 | $146 | Checking!F2:F3 |
| Card purchases and fee: $92 + $480 + $18 + $36, less $45 merchant refund | $581 | ‘Credit Card’!F2:F6 |
| Checking autopay incorrectly included in spending | $480 | Checking!F4 |
| Combined total before excluding repayment | $1,207 | ‘Spending review’!B6 |
| Confirmed repayment excluded | −$480 | ‘Spending review’!B7 |
| October net spending | $727 | ‘Spending review’!B8 |
Exclude the matching payment credit from spending and income too, even when it posts in the reporting month. Keep merchant charges and fees, and subtract merchant refunds. Monarch documents why card repayments are transfers and why both payment postings should be kept out of spending totals.
What about the two $125 debits?
Checking!B5:F5 and ‘Credit Card’!B8:F8 both show November 4 debits of $125. There is no matching card payment credit or shared reference. Keep the parking purchase as a November purchase. Leave the checking debit unclassified until you have evidence. Neither belongs in October.
The review formulas use these specific practice rows. B6 totals October outflows after merchant refunds, B7 tests the supplied payment pair, and B8 subtracts B7 from B6. Extend ranges and review each candidate before applying this to a new export.
Try the source tabs in Jolene.pro
Open the workbook in Jolene.pro and select Checking and Credit Card as the source tabs.
Calculate net spending for October 2026 by posted date. Look through November 4 for payment credits supporting October transfers. Show outflows after merchant refunds, confirmed transfers excluded, and net spending. Explain the two $480 debits and which credit supports a transfer. How should the two November $125 debits be handled? Cite the source cells behind each decision.
Compare the answer with $1,207 before exclusion, $480 excluded and $727 net. These figures and references were checked independently against the synthetic workbook. A live Jolene.pro result has not been verified.
For another matching exercise, see multiple customer payments against one bank deposit, including two competing slips with the same total. Browse more Workbook Studies, or try your own exports at Jolene.pro.