Skip to main content
Solved

Leave field blank if conditions aren't met

  • March 30, 2023
  • 6 replies
  • 44 views
P
Forum|alt.badge.img+11

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"))))

 

 

Best answer by Alexey_Gusev

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

6 replies

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • Answer
  • March 30, 2023

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

P
P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • March 30, 2023

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?

Alexey_Gusev
Forum|alt.badge.img+25

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

P
Forum|alt.badge.img+11
  • plyske
  • Author
  • Inspiring
  • March 31, 2023

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?

Alexey_Gusev
Forum|alt.badge.img+25

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.

Alexey_Gusev
Forum|alt.badge.img+25

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?


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.

Terms & ConditionsAccessibility statement