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
 

Some videos you may like

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
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top