When performing mathematical operations, specifically subtraction, an unusual behavior occurs in a particular edge case. Suppose we attempt to subtract two dates that have the same value, expecting a result of zero. However, instead of the anticipated outcome, the formula inexplicably produces the ‘-’ character as the output.
Expected Behavior:
The expected behavior in this scenario is for the formula to return a value of zero, indicating that the two dates being subtracted are indeed identical.
Steps to Reproduce:
To recreate this issue, please follow these steps:
Subtract two identical dates/times like: Today() - Today() or Duration(0, 0, 0, 0) - Duration(0, 0, 0, 0).
Observe the result.
Actual Result:
Instead of returning the expected zero as the result, the formula outputs the ‘-’ character.
Impact:
While this bug won’t make the website crash or mess up the data, it can definitely confuse users, especially those who rely on precise date calculations. Imagine wanting to figure out the time difference between two date times values and then use that result in an “if” statement to perform specific actions, something like If(thisRow.End - thisRow.Start != 0, 'do something', 'do something else'). Well, this bug messes up that calculation, making it difficult to determine if the two date times are actually the same. So, fixing this problem would greatly improve data precision and the user experience overall.
P.S. A workaround, for now, is checking if the dates are not equal first then do the subtraction.
… is actually auto-interpreted as a duration (per your screenshot) and this is simply how durations display the value 0 (when there’s no duration to return).
It always worked like this (AFAIK)
The hyphen (-) is not just a character here. It is still equal to 0 under the hood.
See the very quickly created sample below where:
The field [End - Start (Number)] : simply return value in the duration field [End - Start] but in a Number field
The field [End - Start (ToNumber)] : Actively convert the duration returned in [End - Start] into a Number but in a Text field (using ToNumber())
The field [End - Start = 0 ?] : Checks if the value in the duration field [End - Start] is equal to 0
Data types such as Date, Time, Date/Time and Duration are all numbers behind the scene so you can actually do any math you want with them .
Oops! I just had an “Aha!” moment . It just dawned on me that I can actually convert the duration data type into a number: Duration(Today() - Today()).ToNumber() = 0 is true.
Silly me, I completely overlooked this thing . Sorry for the oversight. @Pch, you’re truly brilliant and I can always count on your fantastic explanations. Thank you for your invaluable insights as always! Let’s keep building
Kudos to you, @Xyzor_Max , for chiming in. Excellent observation! It never crossed my mind that attempting to convert ‘-’ to a number would actually yield a hyphen.
indeed the " - " is problematic for various reasons. You can calculate with it, but people rather see a zero instead.
I suggested in my blogs also to look into negative numbers. The current state of affairs is that the past is expressed as ‘ago’, thus 8 hours ago, while in many calculations you need - 8 hrs and not 8 hrs ago.
The duration logic would benefit from some serious thought
Maybe the comments of @Xyzor_Max , @Mahmoud and @Pch are a good starting point for the coda folks to pick it up