Keeping a date type column empty if a particular expression shouldn’t apply to it

AppSheet AppSheet Q&A
1

Hi all,
In an app I’m making, one date type column [Date A] has an expression to run in the Autocompute section based on another date type column [Date B]. The expression works fine, but if that [Date B] column is left without being filled by the app user, I would like that [Date A] column (which only gets filled based on the date in the [Date B] column) to remain blank too.
But unfortunately, it seems that it doesn’t work if an expression has been written on to the Autocompute section. I tried placing an “IF” expression as in IF(ISNOTBLANK([Date B]),” ”) but it still brings an error stating that the [Date A] has an invalid value, which I do understand because leaving a date type column blank by this “ “ ought not work actually. I was just wondering if there’s any other way to keep the [Date A] column blank if [Date B] is left blank by the user.

Thank you.

2

Instead of using IF() use IFS()

  • The difference here is that IFS() takes “pairs of parameters”
  • and (most importantly here) the default fall-back is null - not blank… null, as in no value at all - not even a blank value.
IFS(isnotblank([Date B]), 
  *my calculation stuff here*
)

This is what I’ll use when calculating a duration - which requires two dates:

IFS(isnotblank([Ending_Date]), 
  [Ending_Date] - [Starting_Date]
)
  • This only holds a value when there is an [Ending_Date] value, otherwise it’s blank.
4 Likes
3

@MultiTech_Visions Thanks so much. I tried this, but still it returns the same error.

4

@MultiTech_Visions the expression is a little different to what you’ve tried.

IFS(ISNOTBLANK([LMP]),(EOMONTH([LMP], 8) + DAY([LMP]) + 7))
Here LMP is the name of the date type column. I learned this EOMONTH expression from @Steve
Anyhow, this returned the same error.

5

Please post a screenshot of the configuration screen for the Date A column.

6

@Steve Hi, This did solve a smiliar problem for me!! thanks