Change this formula from monthly to biweekly

[kookejar]

Hello,

I am using the Dues Tracker template from Microsoft to track dues in my organization however, dues are paid biweekly for the pay period ending. I do not know how to change this formula from monthly to biweekly and any help would be greatly appreciated...

=IFERROR(IF([@[First OP Date]]<>"",([@[Months member]]*MonthlyDues)-[@[Total Paid]],""),"")


Thanks,

Kookejar

 

[BSALV]

How is the structure of your data ? (use the XL2BB-tool to show that).
What week is the beginning of your biweekly period, how do you know that ?
Does that change next year ? A year isn't exactly 52 weeks ...

 

[kookejar]

Hello Bsalv,

I am new to this forum and just downloaded and installed the XL2BB addin. Not sure how to upload the range as the spreadsheet has a bar graph associated with the results from the formula I posted. Any help with that would be great. I just requested the payroll calendar to be certain of the payroll periods for the bi weekly dues. As soon as I get that information I will get back to you.

Thanks so much for the reply...

 

[kookejar]

Hello BSALV,

Bare with me here. Below is what the XL2BB copied to my clipboard so I pasted it here. Not sure if this is what you are looking for but here it is.

OFF PAYROLL DUES TRACKER TEMPLATE TEST BIWEEKLY.xlsx
	B	C	D	E		G	H
8	BETTY PAGE	example8@domain.com	xxx-xxx-xxx	1/15/2022		$0.00	$14.00
Dues Tracker

Cell Formulas
Range	Formula
H8	H8	=IFERROR(IF([@[First OP Date]]<>"",([@[Months member]]*MonthlyDues)-[@[Total Paid]],""),"")

For the year 2022, the first pay period ending date is 1/15/2022. If a member is on a particular file on this date, they will be off payroll and will owe dues for that pay period.

The image that I uploaded is what the Dues Tracker looks like and when you add the member, the first op date is when we begin tracking the dues owed. Total due each month is now $7 on the spreadsheet because this goes by month however, a member pays $3.50 per pay period and that is biweekly and the reason I need to change to biweekly. The total due is the field where the formula is that I thought could be changed to reflect biweekly rather than monthly.

Hopefully this this post is correct and I do appreciate you looking at this.

Regards,

Kookejar

 

[BSALV]

i'm not sure if this is the way you want it.
Now i substract the "First OP date" from today and divide by 14 + take the integer part multiply by 3$/period.
That's your biweekly saldo , but do you want the next last saturday of your biweekly period instead of today and do you also want to do something with the first OP date ?

Map1
	A	B	C
8
9		pay period	$3,00
10
11	First OP data	total paid	total due
12	15/jan	$0,00	$9,00
13	1/jan	$15,00	-$3,00
14	12/feb	$0,00	$3,00
15	29/jan	$0,00	$6,00
16
Blad2

Cell Formulas
Range	Formula
C12:C15	C12	=IF([@[First OP data]]<>"",TRUNC((TODAY()-[@[First OP data]])/14)*$C$9-[@[total paid]],"-")

 

[kookejar]

Thanks so much for even attempting it and it looks good but let me make sure I understand and am giving you the correct information...

I compare two files to get a list of members that are off payroll. Once a member is off payroll I will add them to the dues tracker. Once the member is on the dues tracker they will owe $3.50 per pay period until they go back on payroll and then are removed from the dues tracker. The First OP Date is the first time that I know they are off payroll and the tracker just accumulates the $3.50 biweekly or per pay period until they go back on payroll. I wanted to make sure you knew that the dues are $3.50 per pay period

To be perfectly honest, I don't understand your last question so I broke it down into two:

"do you want the next last Saturday of your biweekly period instead of today". I honestly don't know what that means and I apologize so feel free to expand on that as I am not an accountant. Which should be pretty clear... ")

"and do you also want to do something with the first OP date ?" The OP (Off payroll) Date is just the beginning of the member owing dues so I think it should just stay as is unless you think it needs to be changed in order to be more accurate.

Just for full disclosure, the issue of off payroll is something that many have tried to solve to no avail. I found this template to give the staff a visual representation of who is off payroll and a somewhat automatic way to track the number of pay periods and the amount of dues the member owes. Any information based upon your expertise is greatly appreciated. If you need more information feel free to let know...

Regards,

Kookejar

 

[kookejar]

Morning Bsalve,

I recreated the spreadsheet exactly as yours in the post however, when I past in the formula I get an error that you see in the image. I did some research and opened up Excel in admin mode to no avail. Any ideas as to why the error pops up?

Regards,

Kookejar

 

[BSALV]

this is a formula within the same listrow of a table, but i see now that you're using a listcolumn "@[First OP Date]" and mine is "@[First OP data]", so change that "a" in an "e"

 

[kookejar]

Hello Bsalv,

Understood...I did change my sheet to reflect yours and still got the error...

 

[kookejar]

Hello Bsalv,

This is what I created using your template so to speak however, I added three columns for the SSN, Last Name and First Name. I edited the formula to reflect "First OP date". This is just another blank sheet that I added to the original that I was using. No matter what I do, I still get the error the error

The syntax of this name isn't correct.
Verify that the name:
Starts with a letter or underscore
Doesn't include a space or character that isn't allowed
Doesn't conflict with an existing name in the workbook

OFF PAYROLL DUES TRACKER TEST.xlsx
	A	B	C	D	E	F
9		Per pay period	$3.50
10
11	SSN	Last Name	First Name	First OP date	Total paid	Total due
12	012-23-2345	MARLEY	BOB	1/15/2022	0
13
Sheet1

The formula is the same as yours where the monthly dues owed is C9...
=IF([@[First OP date]]<>"",TRUNC((TODAY()-[@[First OP date]])/14)*$C$9-[@[total paid]],"-")

I noticed in your previous post that you mentioned listrow and listcolumn. I have not done anything regarding those two and was wondering if I am missing something that needs to be formatted.


Thanks Again.

I do appreciate all your help...

Kookejar