Formula Help - if cell = X then show network days

jacquedale
jacquedale ✭✭✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Laurie Olson
    Laurie Olson ✭✭✭
    Answer ✓

    Give this a try:

    =IF([Date Closed]@row <> "", NETWORKDAY([Date Open]@row, [Date Closed]@row), NETWORKDAY([Date Open]@row, TODAY(0)))

    · Share on FacebookShare on Twitter
  • jacquedale
    jacquedale ✭✭✭✭✭

    @Laurie Olson you are a life-saver that worked perfectly!! Thank you so much.


    Jacque Smith

    Project Controls, MSR-FSR

    · Share on FacebookShare on Twitter
  • Laurie Olson
    Laurie Olson ✭✭✭
    edited 03/21/25

    You're welcome, Jacque!

    · Share on FacebookShare on Twitter
  • Laurie Olson
    Laurie Olson ✭✭✭

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!