New Contributor

Hi There,

I want to get the remarks as "RECEIVED" if the ETA TO is greater than current date. how do i formulate it?

My below formula is showing me the opposite result.





2 Replies
best response confirmed by Hans Vogelaar (MVP)



Your current formula is comparing whatever is in the cell ETA TO SHOP with the number 1, so (other than if the field is empty) it will ALWAYS be TRUE, giving therefore the first result.


To compare with today's date, the formula should read**

=IF([@[JOB'#]]="","",IF([@ETA TO SHOP]]>TODAY(),"Received","In transit"))


Now, I don't know exactly what those column headings mean, etc etc, but just reading this suggests to me that you really might want to reverse the two consequences. i.e., if ETA to SHOP is greater than today, wouldn't that suggest that it hasn't gotten there yet, so therefore is "in transit"? It would be "received" if ETA TO SHOP is less than today? Right? 

Whatever: to compare a date with today, it is the function TODAY()  --the pair of parentheses are part of it --that actually returns the value of today's date, and is the valid comparison with another date.


**I didn't check the syntax of your formula itself; just copied yours and changed the 1 you had in there to TODAY() which is what you'd need, assuming that was all that was wrong.

Thanks so much