Quirky Bug Encounter: Mathematical Operations on Dates Resulting in the '-' Character

Bug Description:

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:

  1. Subtract two identical dates/times like: Today() - Today() or Duration(0, 0, 0, 0) - Duration(0, 0, 0, 0).
  2. 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.

Hi @Mahmoud :blush: !

This is not really the case here :blush:

Today() - Today()

… 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) :blush:

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 :blush: .

2 Likes

Oops! I just had an “Aha!” moment :smiley: . 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 :man_facepalming:. 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 :building_construction:

maybe its not a bug as such

but it is quirky

on what planet is a hyphen a replacement for zero?

at least

ToNumber(Today()-Today())

returns a proper zero

while

ToNumber('-')

returns a hyphen

so the hyphen is some kind of mirage!
i do call that quirky :wink:

3 Likes

I agree and never said it wasn’t quirky @Xyzor_Max :wink:

1 Like

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.

hi @Xyzor_Max ,

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 :wink:

Maybe the comments of @Xyzor_Max , @Mahmoud and @Pch are a good starting point for the coda folks to pick it up :wink:

Cheers, Christiaan

2 Likes

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.