Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Joining two formulas, IF and NETWORKDAYS
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
Comments
-
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.
-
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!
-
We can certainly work around that! What should happen if Offset is blank?
-
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).
-
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.
-
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.
-
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.
-
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.
-
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
-
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.
-
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
-
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
-
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?
-
You are missing your Grey result.
Craig
-
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.
