Calculate paydays since a bill was last paid

Question

Level 1

111 points

Calculate paydays since a bill was last paid

I want a cell that calculates how many paydays have occurred since a bill was last paid. I have monthly, bimonthly, semiannual, and annual bills (I actually also have a semimonthly bill, but I just made it into two monthly bills). I keep trying different things, but it always gets something wrong.

Here’s what I have so far; it almost works, but I feel it’s also overly complicated and perhaps needs reworked from scratch.

IF(MOD(IF(DAY(TODAY())<Payday::$A$1,MONTH(TODAY())−1,MONTH(TODAY()))−(Start Month Rent+IF(Due Date Rent<Payday::$A$1,−1,0)),LOOKUP(Frequency Rent,Interval Values::$1:$1,$Interval))=0,IF(AND(DAY(TODAY())≥Payday::$A$1,DAY(TODAY())<Due Date Rent),LOOKUP(Frequency Rent,Interval Values::$1:$1,$Interval),0),MOD(IF(DAY(TODAY())<Payday::$A$1,MONTH(TODAY())−1,MONTH(TODAY()))−(Start Month Rent+IF(Due Date Rent<Payday::$A$1,−1,0)),LOOKUP(Frequency Rent,Interval Values::$1:$1,$Interval)))

“Start month” is the first month that the bill is paid during a year, for non-monthly bills.

The lookup table tells the formula what the bill interval is in months, so for “semiannual” it returns “6”.

Bills are always paid on the due date.

Paydays are monthly (on the 6th).

Help please? I’m having trouble wrapping my head around this.

(I’m on macOS Monterey, so I can’t use e.g. LET)

MacBook Pro 15″

Posted on Dec 9, 2025 4:07 PM

Reply

Answer 1

Camelot

Level 9

58,275 points

Dec 10, 2025 10:53 AM in response to Zarquon42

I took a look at this, but couldn't work it out since there are too many missing parts. I can't see the structure of your tables to know what values I'm working with, nor quite what results you're aiming for.

I thought I had it - one (single cell?) table called 'Payday' which has the day of the month you get paid (the 6th?), and another table of expenses with data for the day of the month they're due, the start month (for some reason I can't quite fathom), and the interval. But your formula references 'Frequency', and I don't know what this is.

Overall, though, I do think you're overcomplicating this by putting all the logic into a single cell. LET() would certainly help, but that's not an option. Instead (if my visual image of what you're trying to do is right) I'd look to flip the heavy lifting and add a 'next due' column to the expenses table. It's easier to work out on a per-expense basis when the next payment is due, then your main table just needs to compare TODAY() to the next due dates, which is pretty simple.

Dividing the work (i.e. aligning the next due date with the expense) will be a lot easier than trying to craft an uber-formula that does this in one cell.

Reply

Answer 2

Zarquon42 Author

Level 1

111 points

Dec 10, 2025 11:35 AM in response to Camelot

I certainly don’t mind spreading this among multiple cells. What should go in the “next due” field?

Here’s the toy table I’ve been using to try to figure this out (ignore the values in “Paydays since paid”; that’s what I’m trying to figure out):

I’ve gotten rid of the lookup table for simplicity, and added a “today” field so I can see how it works on different dates.

The “start month” is for longer-than-monthly intervals. So if it’s December, and it’s a bimonthly bill starting in January, it’s not paid this month, but if it is first paid in February (which happens to be the case here), it is.

”Frequency” is monthly, bimonthly, etc. Converted here to the interval in months.

Reply

Answer 3

Camelot

Level 9

58,275 points

Dec 10, 2025 12:53 PM in response to Zarquon42

OK, that visual helps a lot, and I have a few idea... first, though, what's your goal here?

You have multiple entries for each expense (e.g. 3 x 'Ameren', 3 x 'Rent', and 6 x 'Amazon'). Are you trying to look ahead to the next 3 payments? or the last one and the next two? How are you identifying which months' payments you're looking at?

I could see this make sense if you wanted the table to show you the last payment and future ones, but I don't see how this table gets you there.

What's the point of the 'Today' field in your table. it seems to have various dates, therefore can't really be 'today', and I can't see any calculation that seems to make sense. Additionally, you seem to have three December payments for 'Rent' (assuming 12/10 means December 10th), or is that an artifact of the formulas not doing what you want?

Reply

Answer 4

Zarquon42 Author

Level 1

111 points

Dec 10, 2025 3:43 PM in response to Camelot

That was a dummy table for testing (the real table has a bunch of other fields, so this was simpler to share). I don’t have three rent payments. There’s just a lot of different permutations: Interval, start month, whether the bill date is before or after payday, and whether today is before or after the bill date, and before or after the bill date. It’s got to work for all combinations of those options, so I was trying all of the different possibilities. Like I said, I added a “today” field so I can see how it works on different dates. Normally it will just use the Today function.

So I save up money from each paycheck to pay bills. If it’s a monthly bill, it’s the whole amount; if it’s bimonthly, I save up half every month, etc. I need to know how much should be in the “Bills” account at any given time. The result here (in months) will be multiplied by the monthly bill amount (Amount/2 for bimonthly, for instance).

Here’s what the finished product should look like. I’m trying to get the “Months Since Paid” field working. It currently shows (manually entered) correct values.

Reply