"Arithmetic expression does not have valid input types"

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

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 :slight_smile:

Thank you so much!

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?

4 Likes

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. :slight_smile:

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?

Is the Expiry Date column of type Date?

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.

1 Like