# Determining days passed using NETWORKDAYS and IF formula

#### mroche

##### New Member
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())

MRoche

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Russell Hauf

##### MrExcel MVP
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!

Replies
5
Views
44
Replies
1
Views
56
Replies
0
Views
209
Replies
1
Views
239
Replies
2
Views
249