Determining days passed using NETWORKDAYS and IF formula

[mroche]

I have 3 date columns.
1. Assigned Date (C)
2. Due Date (D)
3. Complete Date (G)
4. Elapsed Days (H)

Trying to determine the number of days past due date when the Complete Date is blank. The Excel sheet is exported from a Sharepoint site with these fields.

I created an additional column in the sheet and called it elapsed days.

Using the formula =NETWORKDAYS(D2,G2) I get the number of days since elapsed after Complete Date (Again providing a date is provided in the Complete Date field). When there is no complete date, the Elapsed time in Col. H is a - number. For instance Col. C Date 8/4/2014, Col. D Date 9/12/2014, Col. G Date Blank, Col H number is -29925.

The counts are accurate providing there is a date in Col. G. Figuring some form of an IF statement is needed to perform the calculation and enter a number in Col G. such as the example above, the number would be a negative number of -3 as the due date has passed 3 days ago. If the submitter completed ahead of time it could be a positive number.

Tried this but to no avail =IF(g2=" ",NETWORKDAYS(g2,IF(g2="date",g2,TODAY())

Thanks in advance!

MRoche

 

[Russell Hauf]

Personally, I'd probably name the field, "Days Late," and use a formula something like this:

=IF(VALUE(G2)=0,NETWORKDAYS(D2,TODAY()),NETWORKDAYS(D2,G2))

However, NETWORKDAYS presents a bit of a problem in that if the dates are the same, then you still get a value of 1. Hopefully this formula can give you a start - let us know if you run into anything else!