Sometimes Benchling needs to fix bugs or make improvements to our formula evaluation system. This article is a running log of those changes. With each update, we'll also explain what you can expect to see in your Notebook entries when formulas are affected. For In Progress entries, impacted cells will update the next time the entry is edited:
- Previously submitted cells will show a blue dogear and hovering over it will display the old value so you can see what changed
- Previously unsubmitted cells will simply display the new formula result. You can always use version history to review prior values.
Entries that are Approved or In Review will never change. When viewing historical entry versions in version history, formulas may need to re-evaluate in order to display data. In these cases, affected cells will behave as described above and a banner in the entry header will let you know that a live evaluation is occurring. In rare cases, formulas in In Progress entries may also re-evaluate automatically when you load the page, even without making an edit.
If you encounter an out-of-date flag or unexpected behavior in your tables following these updates, simply review the data for accuracy and resubmit the unsubmitted rows to ensure the latest evaluation data. Note that downstream formulas will also be impacted by these changes and will require resubmission to reflect the updates. This action will resolve most discrepancies caused by the formula updates.
Key Issues Addressed
Below are the specific issues being resolved as part of the December 2024 of updates:
1. MIN and MAX for Dates
Issue:
The MIN and MAX functions do not work correctly for date values, returning a value of 0 instead of the expected earliest or latest date.
Example: Attempting to use MIN to find the earliest date in a range results in 0, as does MAX.
Environment: Notebook
Resolution:
These functions have been corrected to properly evaluate and return the minimum or maximum date from a range of date values.
2. Large Number LEN Fix
Issue:
For numbers with more than 21 digits, the LEN function incorrectly returns the length of the number in scientific notation rather than the actual digit count.
Example: A number with 22 digits is evaluated as a length of 5 (e.g., "1.23E+21") instead of 22.
Environment: Notebook
Resolution:
The LEN function now correctly calculates the length of numbers with greater than 21 digits.
3. Empty LEN Fix
Issue:
The LEN function applied to empty cells sometimes returned 4 for some empty or errored cells because the cell contents were interpreted as "null".
Environment: Notebook
Resolution:
The LEN function has been updated to consistently return 0 for empty cells.
4. Date Subtraction Precision
Issue:
Subtracting one datetime from another can introduce float precision errors due to the way dates are internally converted and calculated.
Example: A formula like =(D1-C1)*24 might produce small rounding errors when calculating the duration between two timestamps.
Environment: Notebook
Resolution:
Datetime subtraction has been updated to directly subtract the dates without relying on intermediate conversions, ensuring more precise results.
5. Comparing Dates (Left Hand) and Numbers (Right Hand) with >, <, >=, and <=
Issue:
In table formulas, users could compare dates and numbers, such as A1 < B1 or A1 >= B1, when A1 was a number and B1 was a date. However, comparisons where A1 is a date and B1 is a number would fail. This affected the operators <, >, <=, and >=, but not =.
Environment: Notebook
Resolution:
The evaluation logic has been updated to support comparisons regardless of the position of dates or numbers, ensuring consistent results for all relevant operators.
6. Datetime LEN Fix
Issue:
Previously, the LEN function always returned 24 for any datetime value, regardless of its context.
Environment: Notebook
Resolution:
-
-
- The LEN function aligns with the behavior of other Excess text functions for datetime values, but the output length varies depending on the context of the datetime.
- This variability reflects differences in how datetime values are processed internally.
-
7. Array LEN Fix
Issue:
The LEN function for arrays previously counted characters including commas but ignored spaces (e.g., "abc,def").
Environment: Notebook
Resolution:
The LEN function now calculates the length as "abcdef", standardizing results across text functions.
8. IF number coercion fix
Issue:
The IF function previously converted numeric-like string arguments to numbers, so IF(TRUE, "0001", 2) would return 1 instead of "0001".
Environment: Notebook
Resolution:
The IF function now preserves the original type of its arguments.
For any questions or additional support, please reach out to our support team at support@benchling.com