Need help adding "networkdays" to an existing formula

I'm not intuitive enough to figure out how to add this in, and Googling and searching existing threads in various Excel forums (including this one) didn't answer my question.

Here's my existing formula: =IF(LEN(J1790)=0,INT(K1790-NOW()),"") Where Column J is the date received for Document B and Column K is the required due date for Document B.


It was written by our IT guy, but he works off-site and we don't see him that often, besides, it's good for me to understand these things myself (at least a little). At any rate, the formula does a fabulous job of determining when document A was submitted, and if document B is submitted in 21 days or under of Document A. Unfortunately, it doesn't take into consideration weekends and holidays, and so it can appear as if the vendors are late, when really they aren't.

Thanks in advance!

Originally Posted by martindwilson

that implies that date and time is relevant as now() =date and time whereas today() is just date is that correct?

I guess I should have included all of the associated columns. I hope I'm answering your question correctly, the time isn't really relevant on any of these, just the date itself. So it doesn't have to be "now" specifically, is there a "today" or "current" or something that will work for the current date? (I didn't write the original formula, and am still in babyhood re: the more complex formulas).

Ace's formula comes close, but doesn't cause my "yes/no" column to properly change based on whether it's late or not. I apologize, I mistakenly assumed that my related columns would automatically change if I fixed the "days til due" column, (which is where the original formula resides). At any rate, there are five related columns, which combined return information regarding the two documents. The first is the date Document A arrives and it has no formula . The second is the "days til due" column, which contains the formula I included in the original post, it counts down the 21 days and turns color as the document approaches the due date and is overdue. The third is the "submitted by due date" column which returns a Yes (unformatted), or a No (with pink cell and red font). Its formula is: =IFERROR(IF(K6-J6>0,"Yes","No"),"")

The fourth column contains the date for the matching Document B arrival and it contains a formula which allows the appropriate document A to be matched to document B based on entries in a separate worksheet (sorry I know this is complicated). That one is necessary because the filenames between the two don't always contain easily matchable data (thank you vendors!). They have changed them recently so that they match up better, but the matching formulas are still necessary.

The last column is simply the date Document A arrives +21. There are also a number of index columns which help break down the parts of the documents' (half excel files, half pdf files) filenames. They're named based on their specific job number, parts number, etc (vendor choice, not ours). The spreadsheets' filenames contain the job number, the shipping number and unique number identifier. The pdf files contain a grouping of Document As and while they normally have the job number in common with the spreadsheets some of the other parts of the filenaming convention don't match, which is where the index columns and secondary spreadsheet comes into play (this may be more complex than what can be accomplished here?).

I actually made a quick and dirty fix to this for now. I changed the simple formula in the fifth column to +28 instead of +21
(based on an average 21 day period containing ~3 weekends). Then I created some hidden columns so I can spot check for networkdays correctness
. It seems to be correct so far. That's okay for now, but I'd really like it to be a "real" formula since of course not all 21 day periods will contain the same number of weekends.

At any rate, if this is too confusing for what can be accomplished I'll understand.

Okay, I'm an idiot. I was trying to make this WAY too hard. It's not my days til due column that I needed to re-do. I was on the right track by adding 28 days to the due date column. Anyway if it helps anyone else, here's the actual formula that was the fix. (argh).

=workdays(c3, 21). Works perfectly and makes my other columns still return the right thing.