# 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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
2
Views
279
Replies
1
Views
254
Replies
1
Views
284
Replies
7
Views
396
Replies
7
Views
503

1,216,109
Messages
6,128,877
Members
449,476
Latest member
pranjal9

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back