Issue with formula and different condition types

Hi!

I’am facing an issue a formula where I want to calculate the past, current day, tomorrow and days after tomorrow.

The logic of the formula seems to have an issue with different condition data types. For example:

If I use this formula I’m getting “'#NUM!” error for past days.

T(
  ifs(
    {Datum} < today(), "done",
    {Datum} = today(), "today",
    dateDif(today(), {Datum}, 'D') = 1, "tomorrow",
    {Datum} > today(), "In " & dateDif(now(), {Datum}, 'D') & " days",
    "a" = "a", "error"
  )
)

If I remove the line dateDif(today(), {Datum}, 'D') = 1, "tomorrow",, the past days are calculated correctly.

Like I said it seems the ifs( formula can only validate the values if they have the same result data type.

I also had something in my mind that the ifs(-formula is validating every condition in first place and after that is done it is deciding which one is the correct answer! Like…

T(
  if(
    dateDif({Datum}, today(), 'D') = 1, 'tomorrow', 
    ifs(
      {Datum} < today(), "done",
      {Datum} = today(), "today",
      {Datum} > today(), "In " & dateDif(now(), {Datum}, 'D') & " days",
      "a" = "a", "error"
    )
  )
)

And I also switched the first condition to dateDif(today(), {Datum}, 'D') = 1, 'tomorrow', and had the side effect that the decision for past days switched from “#NUM!” to valid but the days after tomorrow switched to “#NUM!”.

First try:
dateDif({Datum}, today(), 'D') = 1, 'tomorrow',
grafik

Second try:
dateDif(today(), {Datum}, 'D') = 1, 'tomorrow',
grafik

@daniel.pan Could you have a look at it? This is an issue similar to another one I reported in our issue list.

Thanks for analyzing this problem!

The problem is related to two facts:

  1. Datedif can’t return a negative value, it return #NUM!
  2. ifs does not handle #NUM! correctly.

The issues will be fixed in version 3.4.

2 Likes

@daniel.pan Thank you very much!

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.