"Arithmetic expression does not have valid input t...

MicLant · 08-05-2020 08:19 AM

Hello!
I am a new AppSheet user and I’m hoping someone can help me out with my formula

Basically I have an Excel formula that AppSheet couldn’t recognize, so I’m trying to tweak it under App Formula for it to work.

Sample Excel Formula: =IF(ISBLANK(A1),“Pending Submission”,IF(AND(A1-TODAY()<=14,A1-TODAY()>0),CONCAT(“Expiring Very Soon - “,A1-TODAY(),” days”),IF(AND(A1-TODAY()<=60,A1-TODAY()>0),CONCAT(“Expiring Soon - “,A1-TODAY(),” days”),IF(A1-TODAY()<=0,“Expired”,“Valid”))))

In AppSheet, I have the columns [Expiry Date] (with a date column data type) and [Expiry Status] (with a text column data type), so I’m editing the formula under [Expiry Status] and my goal is for the formula to return the values “Pending Submission”, “Expiring Soon - ___ days”, “Expiring Very Soon - ___ days”, “Expired” or “Valid”.

My AppSheet formula is:

IFS(
(ISBLANK([Expiry Date])),
“Pending Submission”,
(AND([Expiry Date] - TODAY() <= 60,[Expiry Date] - TODAY() > 0)),
CONCATENATE(“Expiring Soon - “,[Expiry Date] - TODAY(),” days”),
(AND([Expiry Date] - TODAY() <= 14,[Expiry Date] - TODAY() > 0)),
CONCATENATE(“Expiring Very Soon - “,[Expiry Date] - TODAY(),” days”),
([Expiry Date] - TODAY() <= 0),
“Expired”,“Valid”
)

I’m not entirely sure if I got everything else correct, but the recurring error for the formula above is “Arithmetic expression ‘(([Expiry Date]-TODAY()) <= 60)’ does not have valid input types.”

I’ve tried looking into other articles and questions within the community to look for answers but I haven’t had any luck. However, if anyone has any leads on certain guides or articles that I may have overlooked, please let me know so I can check them out and learn more

Thank you so much!

Steve

Steve

Here’s your expression, reformatted for clarity:

IFS(
  (
    ISBLANK([Expiry Date])
  ),
    “Pending Submission”,
  (
    AND(
      [Expiry Date] - TODAY() <= 60,
      [Expiry Date] - TODAY() <= 0
    )
  ),
    CONCATENATE(
      “Expiring Soon - “,
      [Expiry Date] - TODAY(),
      ” days”
    ),
  (
    AND(
      [Expiry Date] - TODAY() <= 14,
      [Expiry Date] - TODAY() > 0
    )
  ),
    CONCATENATE(
      “Expiring Very Soon - “,
      [Expiry Date] - TODAY(),
      ” days”
    ),
  (
    [Expiry Date] - TODAY() <= 0
  ),
    “Expired”,
  “Valid”
)

You’re missing a condition expression before the last argument, which makes the entire expression invalid. You’ve also got some extra parentheses, but that’s just aesthetic. With some tweaks:

IFS(
  ISBLANK([Expiry Date]),
    “Pending Submission”,
  AND(
    [Expiry Date] - TODAY() <= 60,
    [Expiry Date] - TODAY() <= 0
  ),
    CONCATENATE(
      “Expiring Soon - “,
      [Expiry Date] - TODAY(),
      ” days”
    ),
  AND(
    [Expiry Date] - TODAY() <= 14,
    [Expiry Date] - TODAY() > 0
  ),
    CONCATENATE(
      “Expiring Very Soon - “,
      [Expiry Date] - TODAY(),
      ” days”
    ),
  [Expiry Date] - TODAY() <= 0,
    “Expired”,
  TRUE,
    “Valid”
)

This subexpression looks off:

AND(
  [Expiry Date] - TODAY() <= 60,
  [Expiry Date] - TODAY() <= 0
),

If [Expiry Date] is both less or equal to 0 days and less than or equal to 60 days?

MicLant

Hi Steve,

Thanks so much for helping me out with the format and the correction at the end! Thank you for the reference to Date and Time Expressions too, will look into it.

Sorry about that, it’s [Expiry Date] - TODAY() > 0

However, I still got the same error: “Arithmetic expression ‘(([Expiry Date]-TODAY()) <= 60)’ does not have valid input types.”

Should I do anything about 60, or is it understood by the system that 60 means 60 days?

Steve

Is the Expiry Date column of type Date?

Steve

Subtracting a Date from a Date produces a Duration value, which is not a single value. Please read the doc I shared above. If that doesn’t give you enough to work from, let us know.

Colin_Lough

Hi @Steve
Also struggling with something similar, can you see something I’m doing wrong here?

=IFS([Effective Date]<today(),“Already in effect”,[Effective Date]-TODAY()<=365,“1. Highly imminent: <12 Months”,(AND([Effective Date]-TODAY()>=365,[Effective Date]-today()<(365+(365/2)))),“2. Moderately imminent: <18 Months”,(AND([Effective Date]-TODAY()>=(365+(365/2)),[Effective Date]-today()<=(3652))),“3. Less imminent: <24 months”,[Effective Date]-TODAY()>(3652),“Effective date >2 years”)

Steve

This expression:

([Effective Date]-TODAY())

appears to be subtracting one date (TODAY()) from another ([Effective Date]). The result of this will be a Duration value. A Duration value cannot be directly compared to a Number value as you’re attempting:

(([Effective Date]-TODAY()) <= 365)

Instead, you’ll need to convert the Duration to a Number (easy) or the Number to a Duration (not easy).

To convert a Duration to a Number of days, then compare to a Number of days:

((HOUR([Effective Date]-TODAY()) / 24) <= 365)

"Arithmetic expression does not have valid input types"