Empty field instead of #VALUE!

User Talk Formulas
1

Hi there, I am newbie. I have a simple formular, which gives me in Cell A a date 12 month later then a date in cell B. If there is no date in the cell B, I want cell A to be empty but I get #VALUE!. I tried the ifempty formular, but as soons as I put something like “” in the true part, the cell isn´t in date format anymore…

2

Hi @Max1411,
I think you don’t have to specify something in the else part, for example the formula if(not(isempty("Column B")),dateAdd({Column B},12,"months")) worked when I tried it: it calculates the date if Column B is not empty and displays nothing if Column B is empty (but the column is still considered having a date format).
Hope this helps…

Bests,
Benjamin

3

Thank you @bha. Unfortunately the type changes to string instead of date, which leads to a non european date format and things like date rules for color columns don´t work anymore.

4

Hi again,
Are you sure you use the same formula? In mine, there is nothing like the “” you mentioned (the if formula should look like if(logical, value1, value2) but I just defined if(logical, value1) but nothing happens when the condition is false), and as I said in my last message, the detected format remains european/date.
date_format

Bests,
Benjamin

5

I used your formula:

if(not(isempty("Letzte Validierung")),
dateadd({Letzte Validierung},12,"months"))

As soon as I use that formula it jumps to string. When I use only dateadd({Letzte Validierung},12,"months"), it´s date. I don’t really get it.

6

This is really weird, I can’t reproduce this issue… I thought it might be because I tested on self-hosted enterprise edition whereas maybe you use rather the cloud version, but even on the cloud version I get the same behaviour I already described :thinking:

Maybe @cdb or @rdb would have another idea about that…

Bests,
Benjamin

7

@Max1411 , to bring the example of @bha and yours together, please make a screenshot of the top of your date and formula columns from an unsorted and unfiltered view.

Background: I have the suspicion that while the very first date values in “Column B” in the example of @bha contain a date (in fact, the top one), yours doesn’t. The format detection runs on the very first value of a column. If that is empty (no else clause in the if()), then it runs into #!VALUE, or string (else “”), or an undefined state.

What you just found out is that the automatic format detection of formula colums (and only that, thank god) is a bit of a mess. Three main points which were discussed elsewhere before:

  • Working with heterogenous values as in your case. If my theory is true, you can trick seatable into detecting the format by once making sure that there is a date in the first line, and then trigger the detection.
    • It’s shaky IMHO, because I have no idea whether this will be automatically re-triggered by the system.
    • I just figured out that every formula change seems to trigger a format recognition. Which makes the workflow “change formula” - “make sure that first value is filled” - “re-trigger” - “erase dummy value from first line”
  • The fact that an empty if()-else-clause seems to create a string, and not for example NULL
    • unverified: if(true(),"Hi") equals if(true(),"Hi","")
  • Something I just found out: not even explicit true()/false() constants return boolean.
    • isempty("A") => Boolean
    • if(isempty("A"),true(),false()) => String!
1 Like
8

@abaer you’re right, if I replace the first line by an empty one, the detected format is actually string!

Bests,
Benjamin

PS : seems like a part of your previous message is missing, that’s a pity because it promised to be really interesting!

9

Thanks for verifying!

Yes, my computer crashed. Added the missing bits

@Max1411, so the formula didn’t make the difference, just the value in the first line.

10

@rdb @cdb @daniel.pan here’s my suggestion to fix this, seaparted from my general moaning :wink:

  • Make the automatic formula format detection dependent on the first non-empty value (and not an empty string)
  • Make sure that developers have the chance to distinguish “empty cell” (internally empty in the database) as a return value of a formula from “empty string” (just visually empty).
    • This is just an educated guess, but judging from the Seatable API, a NULL value seems to indicate an empty value internally.
    • If that is the case, introduce a constand null() along with true() and false()
    • Or, for use by non-programmers like me, a non-technical constant empty()
  • Maybe fix an inconsistency like if(isempty("A"),true(),false()) returning string
  • Define or document that an empty else in if(condition,then,else) returns a string
    • Alternatively and in accordance to the above, change it to null (or whatever empty will be)
11

Thank you guys a lot. At least I know the cause now. Any chance to get the #Value! visually away?