Help

I need help to complete this formula

Topic Labels: Formulas
427 0
cancel
Showing results for 
Search instead for 
Did you mean: 
plyske
7 - App Architect
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.},
    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! 

0 Replies 0