Help

Re: Leave field blank if conditions aren't met

Solved
Jump to Solution
2012 2
cancel
Showing results for 
Search instead for 
Did you mean: 
plyske
7 - App Architect
7 - App Architect

Can someone help me modifying this formula so that if the condition 'Trækdato (FarPay)' field is empty, the formula field is left blank? Right now it returns an #ERROR!

 

 

IF(AND(DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10, DATETIME_FORMAT({Trækdato (FarPay)}, "D")<10),
        IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))=2, 
            DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),1, "weeks"), 
            IF(WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"))<2, 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),2-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"), 
                DATEADD(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD"),9-WEEKDAY(DATETIME_PARSE(DATETIME_FORMAT({Trækdato (FarPay)}, "YYYY-MM-10"),"YYYY-MM-DD")),"days"))))

 

 

1 Solution

Accepted Solutions
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
IF({Trækdato (FarPay)},
...your formula...
)

See Solution in Thread

6 Replies 6
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
IF({Trækdato (FarPay)},
...your formula...
)

plyske
7 - App Architect
7 - App Architect

Thanks, @Alexey_Gusev !

Maybe you have time to help me with another one? 

I have this formula:

IF(
  AND(
    {Betalingsmetode} = 'PBS',
    Status = 'Confirmed',
    WEEKDAY({Tilføjet d.}) = 3,
    {Type refundering} = 'Dobbeltbetaling'
  ),
  DATEADD({Tilføjet d.}, 1, "weeks"),
  IF(
    AND(
      {Betalingsmetode} = 'PBS',
      Status = 'Confirmed',
      WEEKDAY({Tilføjet d.}) < 3,
      {Type refundering} = 'Dobbeltbetaling'
    ),
    DATEADD({Tilføjet d.}, 2 - WEEKDAY({Tilføjet d.}), "days"),
    IF(
      AND(
        {Betalingsmetode} = 'PBS',
        Status = 'Confirmed',
        WEEKDAY({Tilføjet d.}) > 3,
        {Type refundering} = 'Dobbeltbetaling'
      ),
      DATEADD({Tilføjet d.}, 9 - WEEKDAY({Tilføjet d.}), "days")
    )
  )
)

Can you help me modify the formula so that if the date returned in the field 'Tilføjet d.' is within the first 10 days of the month, the formula field will return the first tuesday after the 10th day of the month?

Are you sure current formula is right? It shows today for Tuesday, shows next Tuesday for other days, but for Wednesday, it shows next Wednesday

plyske
7 - App Architect
7 - App Architect

Hi, @Alexey_Gusev 

You are correct. I have changed the 3's to 2 instead so that it only takes Tuesday into account. Now it should be correct, right?

It won't solve it. The problem is you are setting 3 options: before Wednesday, Wednesday, after Wednesday.
While you should stay with 2 options.
Tuesday behave the same as Sunday, Monday (I mean don't jump to the next week), Wednesday jumps next week as Thursday, Friday etc. Just include it in their group:
WEEKDAY({Tilføjet d.}) >= 3

Also, I would regroup other expressions, if they are the same. The more repeats and copy-pasting in formula and code, the more chance for human errors.

In short, your formula can be expressed as:

IF(({Betalingsmetode} = 'PBS')*(Status = 'Confirmed')*({Type refundering} = 'Dobbeltbetaling'),
  DATEADD({Tilføjet d.}, 2 - WEEKDAY({Tilføjet d.})+7*(WEEKDAY({Tilføjet d.}) >= 3),"days"))

But the task "find next Tuesday after 10th" is another level, and I can't contribute enough time and efforts from my basic work to help, maybe someone else.