Cell to test if an ETA has passed without an order delivery

GParkins ✭✭✭
edited 12/09/19 in Formulas and Functions


I'm trying to generate a report that shows any orders that have passed their ETA without delivery. In the sheet, I have figured out how to highlight the row with conditional formatting, but I'd like to extract the order essentials into a report that I can surface to three different managers via a portal/dashboard.

To solve the puzzle, I've created a column called "ETA Delay Test", and I'd like to use an =IF(AND formula to test if the date in the "ETA" column has been passed by today's date and the "Date Received" cell is blank (the order has not been received). If the conditions are met, I'd like to drop a "1" in the cell. If the conditions are not met, I'd like to return a blank ("")

I would then use the report builder to evaluate that cell and return data from any row with a "1" in the "ETA Delay Test" column to the report.

My problem is that my formula is "Unparseable," and I can't figure out how to fix it.  Here's what I've tried:

=IF(AND(ETA1<TODAY,["Date Received"]1="","1",""))

Can you help? 


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!