Re: If a date falls between two dates, label field X
0
2634
2
This turned out to be a little tricky, but works pretty well after some tests. This uses two formula fields. First, one that I named {Due Week}:
IF({Next Due}, EVEN(WEEKNUM(DATEADD({Next Due}, 2, "days"))))
This assumes that your pay days fall on Friday. The reason that two days are added to {Next Due} is to ensure that a bill due on Friday will be properly tied to a pay date if it happens to fall on the same Friday (the later tests will make this more evident). If you get paid on a day other than Friday, adjust that 2 accordingly. For example, if your pay dates are Wednesdays, change 2 to 4.
The other formula field, which I named {Paycheck}, does the rest of the job, finding the paycheck that will cover the bill’s due date:
IF({Next Due}, DATEADD(DATETIME_PARSE("1/3/2020"), IF({Due Week} < WEEKNUM(TODAY()), {Due Week} + 52, {Due Week}) - 3, "weeks"))
Again, this assumes that you get paid on Friday. More specifically, it assumes that your first paycheck of this year was on January 3rd. If not, just change "1/3/2020" to "1/10/2020". This also contains logic that’s supposed to work from one year to the next. For example, if the end of December ends in a partial week, that’s actually considered to be Week 1 for the following year, which would mess up the formula if that extra logic were absent. Now, obviously we aren’t actually at the end of the year, so I could only test this code so far, but it appears to work cleanly (example using a 1/3 first paycheck date):
The {Due Week} field can be hidden once the setup is done. As you change due dates, the {Paycheck} value updates accordingly. You can then group by the {Paycheck} field and know which bills fall under each check.
In that case, change EVEN in the {Due Week} formula to ODD:
IF({Next Due}, ODD(WEEKNUM(DATEADD({Next Due}, 2, "days"))))
That looks like it fixes things:
