BUG: Date comparison

Dates that have the same year, month, and day do not always compare equal to each other. I have a table "Outcalls" with a date field "Date" and when I use the following filter, I get one result.

=FILTER(Outcalls, "Outcalls[Date]=$[Date]")

But when I use the following filter to check for dates with the same year, month, and day, I get five results.

=FILTER(Outcalls, "YEAR(Outcalls[Date])=YEAR($[Date]) AND MONTH(Outcalls[Date])=MONTH($[Date]) AND DAY(Outcalls[Date])=DAY($[Date])")

Note that each date value being compared is formatted as a date.

Hi @Isaiah, welcome to the community! Thanks for the feedback on this particular issue. I'm going to explore additional ways around this, but in the meantime, I'm also sending it off to engineering to further investigate.

Hi @Isaiah, I am not seeing the same results as you here. I set up a workbook similar to what you had described, where there is an "Outcalls" table with a column titled "Date" and in the date format.

I then have a screen with a $Date variable and two different lists, one with the =FILTER(Outcalls, "Outcalls[Date]=$[Date]") and the other with the =FILTER(Outcalls, "YEAR(Outcalls[Date])=YEAR($[Date]) AND MONTH(Outcalls[Date])=MONTH($[Date]) AND DAY(Outcalls[Date])=DAY($[Date])"), as you had highlighted.

The result is this:

Basically when I select a date from the date picker in the app that matches a date in the Outcalls table, both lists show the date. I do not get five results.

Could you provide a bit more info here and how your setup differs than what was described above?

Thanks for investigating this. You can recreate the issue most simply in a single table.

  1. Create a new table
  2. Format Column1 as a Date
  3. In A2 enter =TODAY()
  4. In A3 enter =TODAY()+1/24
  5. Format Column2 with the formula =[Column1]=TODAY()

B2 will be TRUE and B3 will be FALSE, despite the same date being displayed in A2 and A3.
honeycode0
honeycode1

The issue is that a Date is actually a DateTime, and if the time value gets changed, two dates on the same day may not be equal. I'm not sure how the time value was modified when I came across the issue originally, but in any case, I would argue that this is undesirable behavior. It should at least be documented.

There is a mirror issue where two times are equal but the dates are different that should be addressed, as well. To demonstrate this mirror issue:

  1. Create a new table
  2. Format Column1 as a Time
  3. In A2 enter =NOW()
  4. In A3 enter =NOW()+1
  5. Format Column2 with the formula =[Column1]=NOW()
    honeycode2

Possible fixes

  1. Document DateTime values and provide a SETTIME(Date, Hour, Minute, Second) or TOMIDNIGHT(Date) function and SETDATE(Time, Year, Month, Day) or TOTODAY(Time) so that Dates and Times can be normalized before filtering or comparing.

  2. Document DateTime values and automatically set the time of all dates to midnight if they are in a cell formatted with Date as opposed to Date Time or Time. Also automatically set the date of all times to the epoch or today if they are in a cell formatted with Time as opposed to Date Time or Time.

  3. Create actual Date and Time values in addition to DateTime values and document them.

This topic was automatically closed after 14 days. New replies are no longer allowed.