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

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?

Find more posts tagged with

Construction

Formulas

Reports

Project Management

Comments

GParkins

Got it!

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

Just a syntax issue.

Senando Benig Jr

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…