NETDAYS with nested IF
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.
Answers
-
@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.
-
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!
-
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
-
That did the trick! I guess I was approaching the logic wrong.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!