Determining days passed using NETWORKDAYS and IF formula

mroche

New Member
Joined
Sep 15, 2014
Messages
1
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top