This is a story about a formula that almost works, and that near-miss is exactly what makes it so familiar. The user, DrBitchin, has already done the hard part: they found a way to count open days while excluding weekends and holidays. They just need the same logic to apply when the item is still open. That gap between a working solution and a complete one is where most spreadsheet users live. It's not a failure; it's a sign that they're thinking in the right direction.
The original formula, `=IF(ISBLANK(T2), TODAY()-B2, T2-B2)`, is elegant because it handles both states, open and closed, with a single check. The new formula for closed items, built around `NETWORKDAYS`, correctly strips out weekends and a holiday list. But when the user tries to combine them, they hit a wall. The problem is structural: the `NETWORKDAYS` version uses a different counting method for closed dates (adding 1, then subtracting a second `NETWORKDAYS` call), and that logic doesn't cleanly drop into the blank-date branch. The result is a formula that returns a wrong number or an error, depending on the data.
The fix is simpler than it looks. For the open-date case, the user needs a version of the formula that counts only working days from the start date to today. That means replacing `TODAY()-B2` with `NETWORKDAYS(B2, TODAY(), Holidays!A2:A8)`. The closed-date branch stays as-is. So the corrected formula becomes: `=IF(ISBLANK(T2), NETWORKDAYS(B2, TODAY(), Holidays!A2:A8), T2-B2+1+NETWORKDAYS(B2,T2,Holidays!A2:A8)-NETWORKDAYS(B2,T2))`. It's one change, but it bridges the two worlds: dynamic tracking while open, accurate counting when closed.
What we appreciate here is the user's instinct. They didn't settle for a static formula or give up when the combination failed. They recognized that the logic they needed already existed in pieces. That's the mindset that turns a frustrating Excel moment into a skill-building one. The spreadsheet itself isn't the goal; the confidence to adapt it is. So take that corrected formula, test it against a few known dates, and then get back to tracking what matters, not wrestling with what almost works.