date = today() problem

[#1]

Hi All,

What is wrong with the below statement? It doesnt seem to return anything.

=IF(date(END_TIME) = TODAY(), END_TIME)

Thanks!

[johnw]

I'm guessing END_TIME is a timestamp?  The date() function is a formatting function only, so you're still comparing a timestamp to a date.  Try using the floor() or daystart() function instead to remove the time portion and leave you with a date.

[#3]

Hi John,

With date(end_time), it does extract the date for me.. just that it somehow doesnt work then i try and use the = sign.

Floor and daystart are not working either.

I am attaching the application to show you the data.

[johnw · Accepted solution]

Floor() and daystart() both worked just fine for me in your file.  Here are the working expressions in case there was some confusion as to what I was suggesting:

=IF(floor(END_TIME) = TODAY(), END_TIME)
=IF(daystart(END_TIME) = TODAY(), END_TIME)

As far as date() goes, date() is a formatting function.  It is FORMATTING the field as a date.  That does not change how it is stored internally.  When you use =, you are comparing the INTERNAL values, not the displayed values.  A timestamp formatted as a date is still a timestamp, so won't match a date unless it happens to be exactly midnight.

Set analysis behaves differently, as it is using the search expression logic internally, which results in a text comparison, so a date() function there WOULD work.  But it won't work in an if() - one of those little internal inconsistencies that annoy me, but I live with.

[#5]

Thanks for the detailed explanation!