If statement to return age of modified row based on certain statuses

Justin650

What am I doing wrong?

=IF([status]@row = “shipped complete to customer”, (TODAY() - [Modified]@row), IF(OR([status]@row = “shipped complete to iti warehouse”, (TODAY() - [Modified]@row)))

Kelly Moore

Hey @Justin650

This is the correct syntax for the formula you posted.

=IF(OR(status@row = “shipped complete to customer”, status@row = “shipped complete to iti warehouse”), TODAY() - Modified@row)

Unless these are the only two modifications on your sheet, the formula will not perform as you expect - every change on the row changes the Modified@row field so your duration could fluctuate. Automations and other formulas can affect the Modified field. It can be tricky to grab the Modified field. In addition, every day , ie TODAY, is advancing that number. Your formula does not have a stop unless you have a move row automation moving it directly off the sheet.

To insure the date is you desire is captured, you could add a helper Date field to your sheet. Using the Record Date automation, record the date when the Status option is changed to one of the above selections. You would use this date field in place of your Modified@row. Not knowing your process, I can only speculate that is what you intended. If this is what you intended, in the Automation wizard the trigger would be when Rows are added or changed, the field would Status with Changes to any value selected. In the Conditions you would add when Status = Shipped complete OR shipped to warehouse. You must click on the AND in the conditions to toggle it to OR.

Did this get you what you needed?

Kelly

Justin650

Thank you @Kelly Moore. I think what I actually need to do is complete this function is reference my data sheet with a helper sheet because the automation will actually modify the row.