How to combine networkdays & IF function

0

Hi,

I'm working to know the following.

Completed - # of calender days taken from sign-up date to completion
In process- # of working days taken from received date to current date (12-7-16 changes using TODAY() )
Pending- # of working days taken from sign-up date to received date

Answer
Discuss

Discussion

Please edit your question and use a descriptive title.
don (rep: 1297) Jul 11, '16 at 3:53 pm
Update your question with the new information so other people who read this page can follow! Also, update your title to be descriptive!
don (rep: 1297) Jul 11, '16 at 4:58 pm
Add to Discussion

Answers

0
Selected Answer

I'm not sure how you want to use the IF statement, or exactly how you want to use the networkdays function since you have different holidays listed for each row, but here is a sample networkdays function that will get you the number of days between the signup date and completion date, assuming saturday and sunday are the weekend and the holiday is in the same row in the holiday column.

=NETWORKDAYS.INTL(C2,G2,1,H2)

If this doesn't help, please edit your original question to include more detail.

Update:

If you are looking to get one of those three values depending on what is in the Current status field, I suggest you do it this way:

  • Put the formulas to get the days for each potential status in their own column.
  • Use 1 formula with nested IF statements to figure out which value to use.
  • Hide the columns with all the days listed by selecting them and changing the color of the text for them to white.

For row 2 do this:

Column I:

=NETWORKDAYS.INTL(C2,G2,1,$H$2:$H$18)

Column J:

=NETWORKDAYS.INTL(D2,TODAY(),1,$H$2:$H$18)

Column K:

=NETWORKDAYS.INTL(C2,D2,1,$H$2:$H$18)

Wherever you want the correct Day amount to appear for row 2, put this formula:

=IF(F2="Completed",I2,IF(F2="In Process",J2,IF(F2="Pending",K2)))

Now, just copy the formulas down for the entire list.

You could put all of this into one formula but it will be MUCH more confusing to maintain and change. My method makes everything a lot easier to manage.

Try this out. I assumed that all of the days in the H column were the holidays that needed to be used for the formulas.

Discuss

Discussion

thank you. Here is what I intend to work at - If the current status is 'in process' can I get to know the number of working days between the dispatch date & completion date. It should also consider the total number of holidays - declared for the entire year. Sorry for not being clear.
Keerti (rep: 2) Jul 11, '16 at 4:12 pm
I'm working to know the following.

Completed - # of calender days taken from sign-up date to completion
In process- # of working days taken from received date to current date (12-7-16 changes using TODAY() )
Pending- # of working days taken from sign-up date to received date
Keerti (rep: 2) Jul 11, '16 at 4:24 pm
You need to update your original question with this info, like I mentioned at the bottom of my post.
don (rep: 1297) Jul 11, '16 at 4:58 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login