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

GParkins

Hello,

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? 

GParkins

Got it!

=IF(AND(TODAY() - ETA1 > 0, [Date Received]1 = ""), "Red", "")

 

Just a syntax issue.  

Senando Benig Jr

Hi

 

Is it possible to give the smartsheet link?

So i can visualize it and conduct proper solution.

Thanks

Andrée Starå

Hi,

If I understand you correctly, I would instead recommend creating a report that checks if the "Date Received" is empty and if the date in the "ETA" is in the past. That would give you a report with late deliveries.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

I second Andree's suggestion. One less column. One less formula. Gives you what you need in the most direct manner.

 

However... Here is the formula if you should decide to go that route.

 

=IF(AND(ETA@row < TODAY(), ISBLANK([Date Received]@row)), 1, 0)

 

 

J. Craig Williams

=IF(AND(ETA@row < TODAY(), ISBLANK([Date Received]@row)), 1, "")

will give you the blank, non zero return value, which is what Andrée's Report suggestion is looking for.

Craig

Shaine Greenwood

Hi! Glad you figured it out.

One thing that I might add is that you don't need the blank return at the end. Formulas will automatically return nothing if the conditions aren't met. This could save you a bit of processing power when Smartsheet calculates. For example:

=IF(AND(TODAY() - ETA1 > 0, [Date Received]1 = ""),"Red")

This formula is the same as yours but the formula engine doesn't have to scan through the extra "" if it's false at the end.