Hi
I am currently trying to create a member-database and I'm struggling, with getting to work different membership-fees based on the join-date during the first year of membership.
The premise is as follows:
Member joins between the 01.01. and 31.03. --> invoice date is 01.04. and the annual fee is 22.50.-
Member joins between the 01.04. and the 30.06. --> invoice date is 01.07. and the annual fee is 15.00
Member joins between the 01.07. and the 30.09., --> invoice date is 01.10. and the annual fee is 7.50
Member joins between the 01.10. and 31.12. --> invoice date is 01.01. and the the annual fee is 30.00
For any following years the invoice date will always be the 01.01. and the annual fee at 30.00.
With the IFS(AND))-formula I can get it to work easily enough, but the big problem is that I need an if-condition for every quarter so I would have to constantly update the formula and the list which is very inconvenient... Currently i came up with the following solution:
A = Join date
B = Next invoice date
C = Next invoice amount
D = 01.01.2022
E = 01.04.2022
F = 01.07.2022
G = 01.10.2022
H = 01.01.2023 etc.
So the formula for the next invoice date based on the join date goes: =IFS(AND(A>=D;A<=E);"01.04.2022";AND(A>=E;A<=F);"01.07.2022";AND(A>=F;A<=G);"01.10.2022"; AND(A>=G;A<=H);"01.01.2023")
And the variable fee-amount based on this invoice date is even less flexible/automated:
=IFS(B="01.01.2022";C;B="01.04.2022";C/4*3:B="01.07.2022";C/4*2;B="01.10.2022";C/4)
So this works fine for 2022 but as soon as 2023 starts I will have to adjust both formulas everywhere and risk messing up all the data of 2022.
I am sure there is a more elegant and simple solution that would work permanently for all years to come? I was hoping somebody here could point me in the right direction how I could best do this in a relatively simple way...
Any help and advice is greatly appreciated, let me know if you need more details about my train of thought. Thank you already!
