Calculating Dates Minus Work Days and Using Sheet Summaries

JPavlasek

Ok, I have a two part question here. I have a client appointment request sheet that gives a Created Date, Client Info, Campus Requested, and such. There are also two check boxes: Client Contacted and Appointment Made. I have a date field next to both of those with an automation so I know what date they checked them.

I have created a "2 Days Past Due" column check box with the following formula
=IF(AND(ISDATE([Created Date]@row), TODAY() >= [Created Date]@row + 2), 1, 0)
followed by conditional formatting that marks the row RED whenever that box is checked but the "Client Contacted" box isn't checked.

So now with the background info, here is what I'm trying to do. I've found a couple of things online but I don't think I'm fully understanding.

- I want the "2 Days Past Due" box to NOT include weekends - I didn't think it was possible but when I was looking for something else, I saw that maybe it is? I've tried a few formulas and I'm getting error messages on all of them.
- I want to set up sheet summaries for each individual campus with formulas for how many days, sans weekends, it took from the Created Date to the Client Contacted Date so I can set up reporting, but once again, I'm getting error message and not fully understanding formulas.

Help?

Protonsponge

Hello @JPavlasek - You could us NETWORKDAYS to help you with your first question -

=IFERROR(IF(NETWORKDAYS([Created Date]@row, TODAY()) > 2, 1, 0), 0)

https://help.smartsheet.com/function/networkdays?_gl=1*t07ao4*_gcl_au*MTk3NzI3NTA2Mi4xNzE2MzkyMDE2*_ga*MTExNzQ4MDA2My4xNjkyMjc5MzQ1*_ga_ZYH7XNXMZK*MTcxODk1NTM1OS4xMTIuMS4xNzE4OTU3MTUwLjU0LjAuMA..

For your second question could you work out the dates between [Created Date] and [Client Contacted Date] in the sheet for each row? I am not sure if you would like to sum the total number of days or just work days between [Created Date] and [Client Contacted Date] but one of the following may be of help to you.

NETDAYS - total number of days between [Created Date] and [Client Contacted Date]

=IFERROR(NETDAYS([Created Date]@row, [Client Contacted Date]@row), "")

NETWORKDAYS - total number of workdays between [Created Date] and [Client Contacted Date]

=IFERROR(NETWORKDAYS([Created Date]@row, [Client Contacted Date]@row), "")

JPavlasek

Great thank you! The first one works perfectly and now my "2 days past due" column is working to count ONLY work days!

For my sheet summaries, I'm getting the #unparseable error when using the network days formula. Yes, I'm wanting it to count how many work days it took between the created date and the client contacted date. I think I see why though. I have MANY rows with information, yet I'm not specifying which row? However, now that I see it like this, I'm thinking that might not be possible. We have over 100 entries now, spread out over 9 campuses. I was really envisioning some reporting on a dashboard to basically state that for Campus 1, out of 15 entries, their average response time is "x" (the average of all of their entries, from created date to client contacted date). I might be getting ahead of myself LOL

Paul Newcome

For the second piece, you would need to put the NETWORKDAYS calculation in the sheet on every row. Then in the Summary Field you would use a SUMIFS to sum this column housing the days based on the campus.