The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# I need help to complete this formula

Topic Labels: Formulas
395 0
cancel
Showing results for
Did you mean:
7 - App Architect

Hello everyone!

So I've written a formula, it works, but I need a few tweaks to it so that it runs exactly as I want it to. The thing is: I am stuck.

The formula looks like this:

``````IF(
{Betalingsmetode} = 'PBS',
IF(
OR(
WEEKDAY({Trækdato (FarPay)}) = 2,
WEEKDAY({Trækdato (FarPay)}) = 5
),
{Trækdato (FarPay)},
IF(
{Trækdato (FarPay)} < DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'),
DATEADD(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'), IF(WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY')) = 3, 0, 7 - WEEKDAY(DATETIME_PARSE('10-' & DATETIME_FORMAT({Trækdato (FarPay)}, 'MM-YYYY'), 'DD-MM-YYYY'))), 'days'),
DATEADD({Trækdato (FarPay)}, MOD(10 - WEEKDAY({Trækdato (FarPay)}), 7), 'days')
)
),
IF(
OR(
WEEKDAY({Tilføjet d.}) = 3,
AND(
{Betalingsmetode} = 'Indbetalingskort',
WEEKDAY({Tilføjet d.}) = 4
)
),
{Tilføjet d.},
{Tilføjet d.},
MOD(
IF(
{Betalingsmetode} = 'Indbetalingskort',
10 - WEEKDAY({Tilføjet d.}),
10 - WEEKDAY({Tilføjet d.}) + 3
),
7
),
'days'
)
)
)``````

I have 6 different fields. I have decribed them here:

Dato for udbetaling: Showing the date of the payment. This is a formula field.

Tilføjet d.: showing the date the record was created. This is a date field.

Type refundering: showing the type of refund which can either be 'Opsigelse' or 'Produktskifte'. This is a single select field.

Betalingsmetode: showing the payment method. This can either be 'PBS' or 'Indbetalingskort'. This is a single select field.

Trækdato (FarPay): showing the date that the money will be paid, IF the customer pays using the PBS method. This is a date field.

Status: showing the status of the payment from the customer. This can either be 'Confirmed' or 'Unconfirmed'. This is a single select field.

I think the easiest way would be to start off with the first problem, have that fixed (hopefully) and then add the next problem in the comments afterwards - otherwise I'll get confused, I think.

So the first problem is this:
If the value of Betalingsmetode is 'PBS' and the date in the Trækdato (FarPay) is before the 10th of the month, then I want the Dato for udbetaling to show me the date of the first wednesday AFTER the 10th of the month.

- two "small" side issues related to the above:

1) If I pick a date in the Trækdato (FarPay) that's one day before a wednesday (fx. 14/3/23), it will give me that date in the Dato for udbetaling, but if I pick the coming thursday as the date in Trækdato (FarPay) (16/3/23) it will give me the correct date, eg. the next coming wednesday (22/3/23) in the Dato for udbetaling

Why is that?

2) If I pick a date in the Trækdato (FarPay) that's a wednesday it won't jump to the next coming wednesday.