I Built a Payment Reconciliation System That Broke on Leap Year

Payment reconciliation can look completely reliable until a calendar edge case exposes a hidden assumption. One of the easiest to overlook is February 29. A system that correctly matches transactions throughout the year can suddenly produce unmatched payments, incorrect settlement totals, or misplaced accounting entries when a leap year adds an extra day. The problem is rarely the date itself. It usually comes from hardcoded date calculations, incorrect period boundaries, timezone differences, or reconciliation rules that assume every month and year behaves the same way.

The Real Problem Was the Date Logic

When I investigated the reconciliation issue, the first thing I checked was neither the payment provider nor the database connection. I looked at how the application interpreted dates.

A payment can have several important timestamps:

• Payment initiated
• Payment authorized
• Payment processed
• Payment settled
• Payment posted to the ledger
• Payment reconciled

These events can happen at different times.

For example, a payment could be initiated late on February 28, processed by the payment provider on February 29, and posted to the internal ledger on March 1.

If the reconciliation logic expects all three events to happen on the same calendar date, the transaction can be incorrectly marked as unmatched.

That is why reconciliation should compare financial events, not simply compare two date fields.

Why February 29 Exposes Weak Systems

Leap years expose assumptions that normally remain invisible.

Common examples include:

• Assuming February always has 28 days
• Hardcoding 365 days in yearly calculations
• Adding fixed numbers of days to determine periods
• Using incorrect month-end calculations
• Comparing local dates with UTC timestamps
• Treating settlement date as transaction date
• Generating accounting periods with static date rules

A system might work perfectly during a normal year because those assumptions happen to produce the expected result.

Then February 29 arrives.

Suddenly, a transaction that should belong to one reconciliation period can appear in another.

The important lesson is that calendar logic should be treated as business logic, particularly when the application handles financial transactions.

Separate Transaction Dates From Settlement Dates

One of the most useful design decisions is to avoid using a single timestamp for every financial event.

A reconciliation record should distinguish between different types of dates.

For example:
transaction_id
effective_at
processed_at
settled_at
posted_at
reconciled_at

Each field has a specific purpose:

• The effective date indicates when the financial event took effect.
• The processed date tells you when the system or processor handled it.
• The settlement date indicates when the external payment network settled the transaction.
• The posted date tells you when the transaction entered the accounting ledger.
• The reconciliation date indicates when the transaction was successfully matched.

This separation makes it much easier to investigate transactions crossing midnight, month-end, year-end, or February 29.

It also prevents developers from using a single timestamp to represent multiple business events.

Stop Hardcoding 365 Days

One of the easiest mistakes to make is assuming every year contains 365 days.

That assumption fails during leap years.

But there is an additional consideration for financial systems: not every financial calculation uses the same day-count convention.

Depending on the application, calculations may use conventions such as Actual/365 or Actual/366.

So the solution is not simply replacing:
365

with:
366

The application needs an explicit business rule defining which day-count convention applies to each calculation.

That rule should be centralized rather than duplicated throughout the codebase.

For example:
calculate_period(start_date, end_date, day_count_rule)

is safer than scattering calculations such as:
days = 365

through different services.

This becomes particularly important for interest calculations, billing periods, subscriptions, settlements, and accounting processes.

Use Real Calendar Calculations

Month-end logic should never assume that February ends on the 28th.

Instead of manually constructing a date, the application should determine the final day of the month using a proper date/calendar library.

Conceptually:

month_end(2025, 2) → 2025-02-28
month_end(2028, 2) → 2028-02-29

The same principle applies to:

• Billing cycles
• Settlement periods
• Financial reports
• Accounting periods
• Revenue calculations
• Refund windows
• Chargeback processing
• Scheduled reconciliation jobs

Calendar calculations belong in a reusable date service rather than being recreated inside individual business functions.

That makes the behavior easier to test and reduces the chance of inconsistent date rules.

Don’t Match Payments by Date Alone

Another weakness I look for in reconciliation systems is overly strict date matching.

Consider this example:
Internal payment:
February 28, 23:58 UTC

Processor settlement:
February 29, 00:04 UTC

A simple date comparison sees two different dates.

Financially, however, they may represent the same transaction.

A stronger reconciliation process should evaluate several attributes:

1. Transaction ID
2. Processor reference
3. Amount
4. Currency
5. Payment status
6. Effective date
7. Settlement date
8. Configured settlement window

A practical matching hierarchy could look like this:
Exact transaction ID
↓
Exact processor reference
↓
Amount + currency + transaction relationship
↓
Configured date window
↓
Exception queue

The important part is that the system should not automatically guess when the available evidence is weak.

An unmatched transaction is easier to investigate than an incorrectly matched transaction.

Settlement Windows Need Clear Rules

Payment providers do not always settle transactions immediately.

A transaction might be processed today and settled tomorrow. Weekends, holidays, processor schedules, and time zones can also affect settlement.

That means the reconciliation logic should explicitly define a settlement window.

For example:
Expected settlement:
transaction_date + configured settlement window

The actual implementation depends on the payment provider and business requirements.

The key is avoiding assumptions such as:
settlement_date = transaction_date

That rule is too simplistic for many payment systems.

A leap year makes the weakness more visible because February 29 introduces an additional boundary into the calendar.

Time Zones Can Make the Problem Worse

Date bugs become even harder to diagnose when different systems use different time zones.

Suppose your application stores timestamps in UTC while a financial institution generates reports using a local time zone.

A transaction near midnight can appear on different calendar dates.

For example:
UTC:
2028-02-29 00:15

Local time:
2028-02-28 18:45

The underlying event is the same, but the calendar date is different.

For that reason, financial systems should establish clear rules for:

• Timestamp storage
• Timestamp conversion
• Business timezone
• Reporting timezone
• Settlement timezone
• Accounting-period timezone

Store timestamps consistently, then apply the appropriate business timezone when interpreting calendar dates.

Idempotency Matters Too

Date handling is only one part of reliable reconciliation.

Payment providers can send the same webhook more than once. Network failures can also cause an application to retry processing.

If the system processes the same settlement event twice, it could create duplicate financial entries.

Every external event should therefore have a stable unique identifier.

For example:
provider_event_id = UNIQUE

When the same event arrives again, the system should recognize that it has already been processed.

This is an important part of secure financial software development because protecting financial integrity is not only about authentication and encryption. Transaction consistency, auditability, idempotency, and controlled processing are equally important.

Keep Financial Records Immutable

A reconciliation system should not silently rewrite historical transactions when something goes wrong.

Instead, maintain the original transaction and record the reconciliation result separately.

For example:
Original transaction
↓
Reconciliation attempt
↓
Matched / Unmatched / Exception
↓
Resolution

This gives the system an audit trail.

If someone investigates a reconciliation issue months later, they should be able to understand:

• What the original transaction contained
• What the processor reported
• How the system attempted to match it
• Why the transaction failed
• Who or what resolved the exception

That is much safer than simply changing the original transaction until the numbers look correct.

Create an Exception Queue

Not every transaction should be automatically reconciled.

When the system cannot establish a reliable match, it should create an exception.

Useful exception types include:

• Amount mismatch
• Currency mismatch
• Missing settlement
• Duplicate payment
• Unknown processor reference
• Settlement outside expected window
• Invalid transaction state
• Accounting-period mismatch

Each exception should contain enough information for investigation.

The system should also preserve the original financial data rather than allowing an operator to overwrite it.

This gives reconciliation teams a controlled way to handle unusual transactions without compromising the underlying ledger.

Test Leap Years Before Production

A normal payment test suite is not enough.

I would specifically test transactions around calendar boundaries.

Normal February

2025-02-28 → 2025-03-01

Leap day

2028-02-28 → 2028-02-29

Leap-day transition

2028-02-29 → 2028-03-01

Year boundary

2027-12-31 → 2028-01-01

Midnight transition

23:59 → 00:01

Time-zone boundary

UTC date ≠ business timezone date

Late settlement

Effective: Feb 29
Settled: Mar 1

Duplicate event

Same provider event received twice

These tests should be automated so that the same edge cases are checked whenever the reconciliation logic changes.

My Reconciliation Checklist

Before considering a payment reconciliation system production-ready, I check:

• Does it support February 29?
• Does it calculate month-end dates dynamically?
• Are transaction and settlement dates separate?
• Is the business timezone clearly defined?
• Are UTC timestamps handled consistently?
• Can settlement cross midnight?
• Can settlement cross the month-end?
• Can settlement cross February 29?
• Is the day-count convention explicitly defined?
• Are duplicate payment events idempotent?
• Are financial records protected from silent modification?
• Is there an exception workflow?
• Can historical reconciliation be audited?
• Are leap-year scenarios included in automated tests?

If these questions lack clear answers, there is still a hidden risk in the reconciliation logic.

Conclusion

The leap-year failure was a useful reminder that financial systems cannot rely on assumptions about how calendars work. February 29 is only one edge case. The same weaknesses can surface at month-end, year-end, midnight, across time zones, or whenever settlement occurs after the original transaction.

A reliable reconciliation system should separate financial dates, use proper calendar calculations, define settlement windows, match transactions using multiple attributes, process external events idempotently, preserve an audit trail, and route uncertain matches into an exception workflow.

The goal is not simply to make February 29 work. The goal is to build reconciliation logic that remains correct even when time, settlement, and accounting rules no longer behave like simple calendar dates.

Frequently Asked Questions (FAQs)

1. Why does February 29 cause payment reconciliation problems?

February 29 can expose hardcoded assumptions about month length, yearly calculations, accounting periods, and settlement dates. Systems that assume February always ends on the 28th can incorrectly classify transactions during a leap year.

2. How should payment systems handle leap years?

Payment systems should use a proper calendar library, calculate month-end dates dynamically, avoid hardcoded 365-day assumptions, and explicitly define how leap days affect settlement, billing, reporting, and accounting calculations.

3. Should transaction and settlement dates be stored separately?

Yes. A payment can be initiated, processed, settled, posted, and reconciled at different times. Keeping these dates separate makes reconciliation more accurate and easier to audit.

4. What should I test in a payment reconciliation system?

Test February 28, February 29, March 1, month-end, year-end, midnight transitions, time zones, late settlements, duplicate events, refunds, and unmatched transactions. These cases reveal date and reconciliation problems that ordinary payment tests can miss.

5. How can payment reconciliation be made more reliable?

Use explicit date rules, deterministic matching, configurable settlement windows, idempotent event processing, immutable financial records, exception handling, audit trails, and automated tests for calendar and settlement edge cases.

Comments

[Rafid Bottler]

Great breakdown. The point about treating calendar logic as business logic really stands out, it's such an easy thing to overlook until a leap year or a month-end forces the issue. The idea of separating effective, processed, settled, posted, and reconciled dates instead of relying on one timestamp seems like it would prevent a whole class of bugs beyond just leap years too, things like DST shifts or delayed webhook delivery would probably benefit from the same design.

The matching hierarchy is also a good call. Falling back to an exception queue instead of forcing a match on weak evidence is the kind of decision that's easy to skip under deadline pressure but saves a lot of pain later. Curious whether you ended up writing property based tests for the date boundaries, or if the fixed set of scenarios in your checklist covered everything you ran into in practice.

[Elsie Rainee]

Thanks, really appreciate you reading through it! You're right that the exception queue point is the one people underestimate the most. Early on I actually had the opposite instinct, I wanted the system to be "smart" and resolve as much as possible automatically. It took a bad matched-in-error incident to convince me that a slower, more cautious system is worth way more than a fast one that occasionally guesses wrong with real money involved.

And yeah, the day-count convention thing came from a similarly painful lesson. I initially treated it as a one-line fix too, swap 365 for 366 and move on. It wasn't until I saw two different services calculating the same period with different assumptions that I realized it needed to be an explicit, centralized rule rather than something every developer reimplements based on their own assumptions.

Glad the settlement window and matching hierarchy sections landed too, those took the most iteration to get right in practice.

[Mayur Upadhyay]

Great writeup, and the point about matching financial events instead of raw date fields is the part I'd want every payment engineer to internalize. It's such a common shortcut to treat settlement date as transaction date, and it works fine until something crosses a boundary you didn't account for.

The matching hierarchy you laid out (transaction ID, then processor reference, then amount/currency/relationship, then date window, then exception queue) is a good model even outside payments. Anywhere you're reconciling two systems, "when in doubt, don't guess" saves you from the much worse problem of a confidently wrong match.

Also appreciated the reminder that day-count conventions aren't a solved problem just because you swap 365 for 366. That distinction between Actual/365 and Actual/366 is exactly the kind of thing that gets glossed over until an audit or a leap year forces the question. Thanks for sharing the checklist too, it's a nice thing to run new reconciliation code against before it hits production.

[Elsie Rainee]

Thanks so much for the thoughtful comment! You're right that the matching hierarchy generalizes well beyond payments. I've started applying the same "confidently wrong match is worse than no match" thinking to inventory sync and even log correlation work, and it holds up surprisingly well.

The day-count convention issue was actually the one that took me longest to get right. My first instinct was exactly what you'd expect, just swap the hardcoded number and move on. It wasn't until a finance teammate flagged that our interest calculations and our billing calculations needed different conventions that I realized the "constant" needed to become a parameter tied to a business rule, not a global default.

Glad the checklist was useful. I've been running it against every reconciliation change since, and it's caught a couple of near misses already, mostly around settlement crossing midnight in a non UTC timezone. That one's sneaky because it looks fine in every test that doesn't specifically target the boundary.

[Vinh Nguyen]

The "use a proper date library" step is necessary but it does not settle period boundaries, because year arithmetic on February 29 is neither invertible nor associative even in a good library. On python-dateutil 2.9.0 with Python 3.14.6, date(2024,2,29) + relativedelta(years=1) gives 2025-02-28, and subtracting a year from that lands on 2024-02-28 rather than where you started. The one that would move money: adding relativedelta(years=4) to the original gives 2028-02-29, but adding one year and then three years gives 2028-02-28. Same four-year span, two different boundaries, and the only difference is whether the scheduler advances from the contract start date or from the last boundary it wrote. So the invariant worth asserting is that every boundary is derived from the immutable start date and never by incrementing the previous one, which is a one-line property test: n steps of one year must equal one step of n years. Worth noting the stdlib refuses rather than clamping — date(2024,2,29).replace(year=2025) raises ValueError — so the loud version and the silent version of the same ambiguity live one import apart.

[Suraj Suradkar]

The exception-queue point is important beyond payments: when evidence is weak, “unmatched” is often safer than a confident guess. I think the same principle applies to AI systems—uncertainty should be a valid outcome, not something the system hides.