Spreadsheets

Assign card swipes to the right statement month in a sheet

Tag each credit card swipe with the statement it will appear on using IF, DAY and EDATE, so you can see how much cash the next card bill needs.

Editorial illustration for Assign card swipes to the right statement month in a sheet

A card purchase belongs to the statement whose cut-off date comes on or after the swipe. If your statement date is the 15th, a swipe on the 14th or 15th lands on this month's bill and a swipe on the 16th lands on next month's. In a sheet, IF with DAY and DATE tags every row with its statement date, and SUMIFS then tells you what each bill will total.

Why does the statement month matter?

A calendar report says you spent ₹30,000 in September, but the card bill that arrives in October may hold swipes from 16 August to 15 September. If you plan cash by calendar month you will be surprised by the bill. Tagging by statement lets you answer the question that actually matters: how much money must be in the account by the due date.

First find your statement date. It is printed on each card statement, along with the statement period and the due date; use those, not an assumption. Put the statement day in a cell, say G1, as 15 for the 15th. These numbers are hypothetical.

The formula that assigns each swipe to a statement

Assume the transaction date is in A2, the amount in C2, and the statement day is in $G$1. In D2, "Statement date", enter:

=IF(DAY(A2)<=$G$1,DATE(YEAR(A2),MONTH(A2),$G$1),DATE(YEAR(A2),MONTH(A2)+1,$G$1))

If the swipe's day is on or before the statement day, the statement is dated in the same month; otherwise it is the month after. DATE copes with month 13, so a swipe in late December correctly rolls to January of the next year. Format column D as a date. An alternative that uses EDATE is:

=EDATE(DATE(YEAR(A2),MONTH(A2),$G$1),--(DAY(A2)>$G$1))

The double minus turns TRUE or FALSE into 1 or 0, so EDATE adds one month only when the swipe falls after the cut-off. Keep the statement day at 28 or below; a day such as 31 does not exist in every month, and DATE would roll into the following month.

A worked example

Swipe dateAmountStatement date (15th)Due date, 20 days later
14 Sep 2025₹2,30015 Sep 20255 Oct 2025
15 Sep 2025₹1,10015 Sep 20255 Oct 2025
16 Sep 2025₹4,00015 Oct 20254 Nov 2025
30 Sep 2025₹75015 Oct 20254 Nov 2025
15 Oct 2025₹60015 Oct 20254 Nov 2025

The 20-day gap is invented; read the real due date off your statement. Add the amounts by statement: the 15 September bill is 2,300 + 1,100 = ₹3,400, and the 15 October bill is 4,000 + 750 + 600 = ₹5,350. To show these automatically, list the statement dates in a column and use =SUMIFS($C$2:$C$500,$D$2:$D$500,F2) beside each.

Which date should I use for the swipe?

Use the date your issuer counts for the cut-off. A bank may post a transaction a day or two after you swipe, particularly around the statement date, and a purchase made on the 15th could appear on the next statement. Compare a few rows from a real statement with your sheet. If a few land in a different bill than your formula predicts, add one or two days to the transaction date in the formula, or log the posting date instead of the swipe date.

How do I plan the cash for the due date?

  1. Create a small table with each upcoming statement date and its total from SUMIFS.
  2. Add a column for the due date, either typed from your statement or computed as the statement date plus the number of days printed on it.
  3. Flag anything due within seven days with =IF(F2-TODAY()<=7,"Pay soon","").
  4. Compare the total with the balance in the account that pays the card.

This also shows when a purchase is best placed. Swipes made just after the statement date generally take longest to reach a due date, which is a timing fact about cycles, not a reason to spend more. Whether to pay in full or carry a balance is a money decision for you, with the charges printed on your statement.

Final checklist

  • The statement day is typed from the real statement.
  • Dates are real dates, not left-aligned text.
  • You tested swipes on the cut-off day, the day before and the day after.
  • Refunds are negative amounts.

If you import card statements into SpendixAI, you pick the account first, so each transaction stays with that card, and you can review the rows before saving.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports