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.},
DATEADD(
{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.
Thanks in advance!