Formula to count days past a certain date without weekends

AGE_UT

Hi!

I have a sheet where one column has a submission/end date ("END DATE: CA Exam 4 Part 2") and another column is a "due by" date ("E4 Window Closes"). I have a column that I keep hidden which has that "due by" date ("E4 window closes") to help in the formula I have running:

=IF(AND(ISDATE([E4 window closes]@row), [E4 window closes]@row <= TODAY()), MAX(0, IF([END DATE: CA Exam 4 Part 2]@row <> "", [END DATE: CA Exam 4 Part 2]@row, TODAY()) - [E4 window closes]@row))

This is pretty good because it is correctly counting the days after the deadline ("E4 Window Closes"), but how can I edit it to where it will NOT count the weekends? (Ex. Exam 4 end date is scheduled for 1/21/25, but the "E4 Window Closes" [deadline] is 1/17/25. I want the formula to give me 2, not 4).

Thank you!

Paul Newcome

I would suggest the NETWORKDAY function:

https://help.smartsheet.com/function/networkday

.

AGE_UT

@Paul Newcome So the "holidays" in that function have to be additional columns? As in, I can't just enter additional dates to not count in the formula itself as a date (they have to be a cell I can select)? Seems like it's going to somewhat clutter up the sheet to have to add new columns that just have holidays, or is there a workaround that I might be unaware of?

Also, is there a way for the networkday function to work "better" with its count? I have start and end dates as 11/8 and then 11/15/24, but it counts that as 6 days rather than 5.

The other problem with the networkday formula (and why I ended up with the formula that I have), is that it's counting days as positive or negative, but I ONLY want the days that are AFTER a deadline. So all other days that happen before the deadline should be 0 (although blank would be ideal).

Thank you for your help!

AGE_UT

Ooh, I think I've got something a little bit better now, implementing a networkdays function in there, but also where it won't give me a count unless the date is after the deadline:

=IFERROR(IF([END DATE: CA Exam 4 Part 2]@row > [E4 window closes]@row, (NETWORKDAYS([E4 window closes]@row, [END DATE: CA Exam 4 Part 2]@row))), "")

Now the only thing is the darn networkday function counting an extra day (like I mentioned, having start and end dates as 11/8 and then 11/15/24, it counts that as 6 days rather than 5). Not sure how to fix that.

Paul Newcome

For holidays, I create a separate "Holidays" sheet and just list the dates down a date type column. Then you can create a cross sheet reference to this column.

Start and end dates of 11/8 and 11/15 would make sense as 6 days because you have the 8th as one day and then Mon through Fri of the next week.