Multiple column NETWORKDAYS functions with SUM and IF function

[ConstructT]

Hi all

I am trying to create a spreadsheet which calculates how many days overdue a folder has been submitted, and also tells the administrator which day the folder should be chased. This needs to exclude weekends, however, the spreadsheet includes multiple columns which should interact with each other as attached

As this spreadsheet is used daily by someone with a busy work schedule I would ideally like to only need to input the due date and return date for ease

Both the chase day and days overdue need to exclude weekends

The days overdue already contains a formula which is the Return date minus the Chase day

The Chase day is always the due date + 7 minus weekends

So I want the spreadsheet to have formulas in each column with these functionalities:

Due date: manually entered date by the admin
Chase day: Due date + 7 minus weekends
Return date: manually entered by the admin
Days overdue: Return date - chase day minus weekends

I also need to include an IF function so that if one of the sections is left blank (as sometime happens) the formulas do not return any result (so IF(G7>0)) WITHOUT using conditional formats because the spreadsheet also contains dropdowns, and if someone uses the dropdown I don't want it to corrupt the conditional formatting which can sometimes happen (unless someone knows a fix for this?)

I have tried a few ways of doing this already but I always find that one part will succeed and the other will fail

Thank you in advance for the help!

Dates spreadsheet.png

[Bernie Deitrick]

With your example table in A1:Dxxxx, use this in cell B2, formatted for date:

=WORKDAY(A2,7,Holidays)

and this in cell D2, also formatted as a date

=IF(C2="","",NETWORKDAYS(B2,C2,Holidays))-1


This assumes that you have a list of holiday dates that you have named "Holidays" - if you want to ignore holidays, take out the ,Holidays from each of these.

Note, too, that your "Days Late" is actually "Days after chase started" - actual Days Late would be

=IF(C2="","",NETWORKDAYS(A2,C2,Holidays))-1

[ConstructT]

That's great, I had to make a couple of changes to match my spreadsheet but otherwise it worked perfectly! Seems I was halfway there, just missing a few key parts of the formulas

Thank you so much for your help!

Out of interest, IS there a way to prevent conditional formatting from being corrupted by a filter? I know it will probably involve adding VBA code, but knowing this would be really helpful in the future.

[Bernie Deitrick]

I have never seen a filter that corrupted conditional formatting - drop downs don't either. Copying and pasting will - could you attach an example where you think the CF is corrupted?

[ConstructT]

Hi Bernie

I have attached an image to show you what happens on some of my spreadsheets when I use a filter on it.

As you can see, the bottom three conditional formats have populated multiple ranges - originally there was only one range each.
It is as if the filter confuses the format range and moves it around the page and multiplies it.

Maybe I have too many conditional formats on these tables?



conditional formats.jpg

[Bernie Deitrick]

That issue is usually created by row insertions not being properely handled by Excel. To fix that, usually, you can delete CF from all but the top row, then copy the top row and paste formats to all rows (including the top row).

[ConstructT]

Perfect, you've solved it for me! Thanks very much!