Hello,
I’m trying to create a bill spreadsheet for myself. I have a column for “Next Due”. I want a field to be labeled what pay period that bill falls into. I get paid bi weekly, so how would be the best way to do this?
Basically I want to be able to group my bills by pay period to see how much is owed during that time frame, what is outstanding etc
Thank you!
+21This 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.
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.
You’re awesome, thanks for your reply! It seems to mostly be working, except:
Just looking at this most recent pay period 13 March - 27 March.
NIB is due 23rd March, Hello Fresh due 19th March. They’re put into different pay periods with that formula?
NIB is showing in week 14, and hello fresh week 12. With paycheck date of March 20th for NIB and March 6th for Hello Fresh. They should both show March 13th for the paycheck?
Thanks again!
+21You’re awesome, thanks for your reply! It seems to mostly be working, except:
Just looking at this most recent pay period 13 March - 27 March.
NIB is due 23rd March, Hello Fresh due 19th March. They’re put into different pay periods with that formula?
NIB is showing in week 14, and hello fresh week 12. With paycheck date of March 20th for NIB and March 6th for Hello Fresh. They should both show March 13th for the paycheck?
Thanks again!
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:
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:
You’re a genius, thank you!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.