Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Formula Help - if cell = X then show network days
I hope my explanation makes sense.
I currently have a basic formula that shows "networkdays" between 2 dates, however it obviously shows "invalid date type" when the "end date" has not been entered
=NETWORKDAYS([Date Open]@row, [Actual Closure Date]@row)
. So we want the cell to show "duration to close" when status is "closed" and there are start/end dates. But when status is "open" we want it to show how many days it has been open.
I tried to do it in separate columns (for ease) since I was struggling, but I can't get anything to work. As a separate column for "days open I have
=IF(CONTAINS("Open", [Issue Status]@row, NETWORKDAYS([Date Open]@row, TODAY())))
Of course if there is a way to combine the two in one formula that would be great.
Thanks in advance for any help.
Jacque Smith
Project Controls, MSR-FSR
Best Answer
-
Give this a try:
=IF([Date Closed]@row <> "", NETWORKDAY([Date Open]@row, [Date Closed]@row), NETWORKDAY([Date Open]@row, TODAY(0)))
Answers
-
Give this a try:
=IF([Date Closed]@row <> "", NETWORKDAY([Date Open]@row, [Date Closed]@row), NETWORKDAY([Date Open]@row, TODAY(0)))
-
@Laurie Olson you are a life-saver that worked perfectly!! Thank you so much.
Jacque Smith
Project Controls, MSR-FSR
-
You're welcome, Jacque!
-
You should also add an automation in a workflow that says if Date Closed is not blank, set the value "Closed" in Issue Status. This saves workers time not having to maintain the status field. I usually also include a backout workflow to change value to "Open" if Date Closed changes to Blank. Otherwise, if you require that the worker change the issue status to Closed, then the formula will need another test added to check for that. The first IF would need an AND statement to test for both the Date Closed and Issue Status values. Another factor I do not see in the data is % complete.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.2K Get Help
- 452 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- How do I edit this formula to turn button yellow when due date is 5 days away. =IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green"))) …
- I need a formula to calculate sets of specific Date columns, and tally those date columns into a % of that set? For e.g. I have 2 groups. Each group has specific columns that make up the set for each …
- Hi, in the image below I have in my "extrusion" column an entry that populates by a formula (in this case "M3406 HEAD TRACK 15' is populating) I'm looking to populate the "Last Cycle Count Date" colum…