NETDAYS with nested IF

shiggins990

I am trying to create a formula that counts number of days since an incident and separate it by a particular department. The date would reference a date specified by user when submitting a form. User also identifies department incident occurs in and I want specific counts for each department.

I am currently working with the following formula,

=IF({Region}, "Warehouse", NETDAYS(MAX({Sheet - Data Input Tracker with Form Range 5}), TODAY()))

Currently getting a return of "Invalid Data" if I change the IF operator to = I get Invalid operation.

Eric Law

@shiggins990 Your IF formula off. IF should be IF(criteria/logic, true, false) and you can only IF 1 cell, not a range. Can you specify what you are trying to do and what criterion you need for it to do what you want it to do.

shiggins990

https://community.smartsheet.com/discussion/comment/408879#Comment_408879

So I have 3 departments Warehouse, Transportation, Culinary that exist in the "Region" column on the sheet I am referencing.

I have a summary sheet I am trying to build metrics out for to feed to a dashboard. For each department I want to know how many days since an incident/accident.

I want the formula to count days since a reported accident based on the reported date column for any given department. Look at most recent date for Warehouse and count, then on the next row do the same for Transportation, etc.

Thank you for your help!

Eric Law

https://community.smartsheet.com/discussion/comment/408881#Comment_408881

Can you post a screenshot of this? There's a few ways to do this. Here is the simplest way to put it in your summary sheet. I'll make up my own column names but hopefully you get what I am trying to do.

=TODAY() - MAX(COLLECT(Accident:Accident, Department:Department, "Warehouse"))

Accident is date of any accident

Department is the name of the department

shiggins990

https://community.smartsheet.com/discussion/comment/408887#Comment_408887

That did the trick! I guess I was approaching the logic wrong.