Formula help (return blank when adjacent calculated cell is blank)

Platform discussions
,
1

I am tracking the status of orders and want to identify any orders that will be delivered after the ‘required on site’ date. I have calculated the ‘days’ between the dates in a column and want to flag any orders that have less than 14 days as critical.

I am using the following formula in my ‘flag’ column - IF({ROS Float (days)}<=14,“CRITICAL”,“”) however if the previous column is blank (dates have not yet been confirmed) then it is flagging the order as critical. I want it to leave the flag column blank if the previous column is blank. If is change this formula to <0 (rather than <=14) then the formula works as i expected.

The previous column (ROS Float) is also a formula ( DAYS({ROS Date},{Shipping Dates#End})) which is why i suspect this isn’t working with the formula above as if there are no dates and therefore no days calculated the cell isnt technically blank?

What do i need to change to achieve the outcome i need?

See screenshot below where ‘critical’ is shown when the ROS float column is blank.

Order status
Order status724×319 35.4 KB

Thanks in advance

2

After a moment of inspiration i have found a solution. I have added an IF statement for the ROS Float column - IF({Shipping Dates}=“”,“To be confirmed”,DAYS({ROS Date},{Shipping Dates#End}))

This now only returns the critical flag when there is a number in the ROS Float column as needed.

Please let me know if there is a better way to achieve this.

Thanks.

3

Hi Leigh,

It is the correct way.

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.