Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
What is wrong with the below statement? It doesnt seem to return anything.
=IF(date(END_TIME) = TODAY(), END_TIME)
Thanks!
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.
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.
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.
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.
Thanks for the detailed explanation!