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!
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?
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?
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.
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โ)
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)
See also:
Thank you, @Steve
User | Count |
---|---|
37 | |
27 | |
24 | |
17 | |
15 |