Joining two formulas, IF and NETWORKDAYS

Phil Smith

Hi

 

I have the following formula but only want it to be run where my task is in a 'Not Started' status.

 

=NETWORKDAYS(Start1, Finish9)

 

Is there a way to run an IF statement first as part of the above formula? I have tried running an IF statment in a hidden column but can't seem to link two together.

 

Thanks

 

Phil

Greg Gates

You can definitely do this! It would look something like:

 

=IF([Task Status]1 = 'Not Started', NETWORKDAYS(Start1, Finish9), "")

 

So if the task status is equal to "Not Started" it will calculate the net work days (did you mean to use values in different rows here?). If it isn't, it will just leave the cell blank.

 

I hope that solves your issue! Let me know if it doesn't work or if you have any questions.

Phil Smith

Hi Greg

 

Thank you for this, yes it worked fine however I'm now trying to expand on some other logic within the same sheet but am struggling to to get the formula to work when there isn't a date in the OffsetC cell.

 

I have two columns which I am using to set the Harvey Ball colours. One column is called "DLeft" and then other "OffsetC". DLeft is a simple number, OffsetC is a date.

 

Formula is working ok until there is a blank date in the OffsetC cell, it then reports as INVALID

 

=IF(OffsetC230 > DATE(2016, 11, 8), "Red", IF(DLeft230 = "", "Gray", IF(DLeft230 >= 5, "Green", IF(DLeft230 = 4, "Yellow", IF(DLeft230 = 3, "Yellow", IF(DLeft230 = 2, "Yellow", IF(DLeft230 = 1, "Yellow")))))))

 

Essentially:

if OffsetC is NOT 'blank' and is greater than 08/11/16 (my project go live date) then 'red' otherwise

if DLeft is greater than 5 then everything is ok 'green'

if DLeft is between 0 and 4 then 'yellow'

if DLeft is less than 0 then 'red'

 

Help!

 

Greg Gates

We can certainly work around that! What should happen if Offset is blank?

Phil Smith

It then uses the DLeft logic and ignores the OffsetC date check

 

I'm looking for 2 x states and then flagging these with a red ball:

 

1. DLeft  is less than 0

2. OffsetC is later than 08/11/16

 

The DLeft logic is just giving a basic indicator or progress (RAG status).

Greg Gates

If I understand it correctly, I think you just need to add an AND statement to your very first condition. It might look something like this:

 

=IF(AND(NOT(ISBLANK(OffsetC230)), OffsetC230 > DATE(2016, 11, 8)), "Red", IF(DLeft230 = "", "Gray", IF(DLeft230 >= 5, "Green", IF(DLeft230 = 4, "Yellow", IF(DLeft230 = 3, "Yellow", IF(DLeft230 = 2, "Yellow", IF(DLeft230 = 1, "Yellow")))))))

 

If the "NOT(ISBLANK())" part fails, Smartsheet won't even bother to check if the offset is beyond the particular date. This means you shouldn't get that invalid error anymore.

 

I hope that fixes your problem! Let me know if that isn't quite what you're looking for.

 

Phil Smith

Hi Greg

 

Almost there. The revised formula works great for when there is a date but is giving an #INVALID ARGUMENT error when there isn't a date.

 

 

Greg Gates

Hmmm. I didn't have any issues when I tried it on a sheet with blank dates. Could you take a screenshot of your screen? Maybe I'm missing something here.

Phil Smith

Hi Greg

 

Link to a sample sheet below:

https://app.smartsheet.com/b/publish?EQBCT=4b554dfa578b4a1c96f27f43a01968f3

 

You should be able to see all of the formula's. There's a hidden column that is just converting the task 'days' to a number which I then reference.

 

Thanks again for your help.

J. Craig Williams

I would say that your AND() is at fault.

You check the first argument is not blank, but in the second argument are comparing it to a date. Sometimes Smartsheet will get confused by cells that had a value (like a date) and then have that value removed. It may be blank or not a date but I've seen instances where I wasn't sure which was which.

 

Also, you can get rid of some of your IF statements -- yellow when <=4 instead of checking each number.

 

Craig

 

 

 

 

Phil Smith

Thanks Craig, I've removed the AND and it is still failing.

 

I wonder if I am trying to over complicate things.

 

Essentially:

if OffsetC is blank then use DLeft logic below, OR:-

if OffsetC is greater than 08/11/16 (my project go live date) then 'red' otherwise:-

if DLeft is greater than 5 then everything is ok 'green'

if DLeft is between 0 and 4 then 'yellow'

if DLeft is less than 0 then 'red'

 

OffsetC is only set with a date when the task status is in a "NOT STARTED" state. I'm counting the task duration days based on a start date of TODAY() to see if I still have time to complete the task.

 

DLeft logic is =IF(Status734 <> "Complete", IF(Status734 <> "N/A", NETWORKDAYS(TODAY(), Finish734), "")) 

 

I'm pretty sure that I need an OR statement that takes into account that the OffsetC could be blank.

J. Craig Williams

You are (over complicating)

 

OffsetC  is looking at Status, - if Not Started it will be blank.

So just look at Status 

 

if (status = not started)

- check for date > go live date

else

- check dleft 

(i'd do them in this order:)

- red, green, yellow

(so <0, >5, else)

 

the check for blank date should not be needed because of the formula in OffsetC

 

Craig

 

Phil Smith

Hi

 

Sorry for being thick..

 

So....if the Status is "Not Sarted", OffsetC is > "go live date" set the ball red, else DLeft logic applies?

 

=IF(Status423 = "Not Started", IF(OffsetC423 > DATE(2016, 10, 8), “Red”,  IF(DLeft423 < 0, “Red”, IF(DLeft423 > 5, “Green”, “Yellow”)))))

 

I'm not sure that I need so many 'IF's?

 

Phil

Greg Gates

I don't think OR would solve your problems here. I still can't recreate your issue on my own sheet I made to test this functionality, and I can't find a way in which our sheets are different. Would you be able to make that example sheet you shared with me editable?

J. Craig Williams

You are missing your Grey result.

 

Craig

Damian Palacio

Hello,

I am trying to create a formula with both IF and NETWORKDAYS that adds 2 days for when it says Critical, 10 days for Expedite, and 14 for standard, but I want to exclude weekends and holidays.  Right now I have =IF(B15="CRITICAL",E15+2,IF(B15="EXPEDITE",E15+10,E15+14)), where E15 is the date sent to the general contractorand the number values, 2, 10, and 14 are the days they are contractually obligated to give us a response.  After I'd like to deduct the date returned from the date sent to give me the total response time.  

 

Thanks everyone.