Apr 22 2022 07:07 PM
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.
Apr 22 2022 08:03 PM - edited Apr 22 2022 08:06 PM
Solution
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.
Apr 22 2022 08:03 PM - edited Apr 22 2022 08:06 PM
Solution
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.