Setup Weekly calculations based upon a particular plan

[J_C]

I am trying to set up Airtable to have an interface where my office staff can get the amounts they need for a meal plan change.

We have multiple plans and each week the cost of the plan reduces by a set amount until the end of the change period. The amount reduced is different for each plan. When someone wants to change their plan we take the prorated amount of the original plan and deduct the prorated amount of the new plan. If the amount is positive then the customer is given a refund, but if the amount is negative the customer is charged the additional fee. In either case, there is a set fee for the change. If they want to cancel the prorated amount of the plan, plus the remaining balance of their point plan, minus the fee is calculated so it can be refunded. There are some cases where the fee would not be applied.

My goal is to have an interface my staff can use to choose the current plan then choose the new plan and the interface would tell them how much needs to be charged or refunded since it would already be accounting for the proration and such.

I appreciate any and all help. Formulas are not my strong suit in the least.

Thank you

[J_C]

Doing some searching and such I have a formula that once a week will increase the deduction amount, but as the formula stands it will only calculate once each week, and it won’t increment the value. I need it to calculate each week and increase the value.

For example each week Plan 1 is reduced by 183.00. So week two it should be 366.00 and week 3 it should be 549. Plan 2 has a different amount it reduces by. How do I take the weekly run below and make it add the needed value?

IF(DATETIME_FORMAT(TODAY(), ‘dddd’) = ‘Tuesday’,{Deduction Amount}+{Deduction Amount})

[TheTimeSavingCo]

Hmm, not too sure I follow what your end goal is, but I’ve put something together here that I think answers your latest question about adding the weekly values together

If you could manually create a table that shows some example data of how you would like your data to show up in an ideal scenario I could provide suggestions on how to achieve that?

[J_C]

I appreciate this. I was actually able to sort out this need by using the SWITCH command. One thing I am having trouble with in week numbers is the WEEKNUM command is returning the wrong week. IT says the week is the 32nd week, but we are in the 31st week of the year.

I am using

WEEKNUM(TODAY(), “Sunday”)

I tried adding the SET_TIMEZONE parameter to the formula, but it didn’t change anything. Maybe my syntax was bad because I am not quite sure how to combine the two functions together.

[TheTimeSavingCo]

J_C:

IT says the week is the 32nd week, but we are in the 31st week of the year.

Hmm! When you say it’s the 31st week of the year, what day does that start on?

As you can see from the screenshot below, if it starts on Monday, it’d be the 31st week, but as you’ve set the start day to be Sunday, it’s week 32 instead