ISBLANK with seperate IF statements

[nero6014]

Good morning, I have the following formula that works well for the 1st part of my requirement but need help to meet the second part of the requirement as well.
=IF(ISBLANK([@[First TAG meeting]]),0, TODAY()-[@[First TAGmeeting]]) work well but once a project is complete and there is a date in the published column I need the formula to do Published - First TAG meeting and ignore the first part. Thank you for any help you may be able to give.


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Project[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <tbody>[TR]
[TD="width: 118, bgcolor: transparent"]First TAG meeting[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 92, bgcolor: transparent"]Published[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<colgroup><col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <tbody>[TR]
[TD="width: 120, bgcolor: transparent"]Total project days[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6/1/2017[/TD]
[TD][/TD]
[TD]today - 6/1/2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6/1/2017[/TD]
[TD]12/19/2017[/TD]
[TD]published - First TAG meeting[/TD]
[/TR]
</tbody>[/TABLE]

 

[nero6014]

I have continued to work on this and the following formula appears to do what I need but is there a way to shorten this up?
=IF(ISBLANK([@[First TAG meeting]]),0,IF(NOT(ISBLANK([@[First TAG meeting]])),TODAY()-[@[First TAG meeting]],IF(AND(NOT(ISBLANK([@[Date Published]])),NOT(ISBLANK([@[First TAG meeting]]))),[@[Date Published]]-[@[First TAG meeting]])))

 

[ajamess]

Going to assume that is d2, where your 0 is, and put this formula

=if(b2 = "" , 0,if(c2="", today(),c2)-b2)

And Drag down

 

[nero6014]

ajamess, not sure how it is that easy but it works and Thank You. I'm not really understanding the logic, if you have time could you please walk me through it?

 

[ajamess]

Glad to hear it worked. Sometimes while digging deep, you can make your solution more complicated than needed (I do it all the time )

ajamess, not sure how it is that easy but it works and Thank You. I'm not really understanding the logic, if you have time could you please walk me through it?

No problem, I'll try to explain the logic in words (I'm not as good at this as numbers and code, so bare with me!)

=if(B2="",0,....

This first part just checks if B2 is "", or blank. (I guess you could do isblank I believe the command is, I just have a habit of using ""). If it is blank, the output is 0.

 ...., if(C2="",Today(),c2)...

If B2 is not blank, it moves on to this next IF. The next two options are, if C2 is also blank, we're going to want, Today's date minus B2's date. If C2 isn't blank, we want to subtract B2 from C2. So we're either going to do Today -B2, or C2-B2. So this If statement, determines the first part of that. Say in

 x - b2

The if decides if x is Today(), or C2.


Sorry if confusing, like I said I'm not great at that. But hope the reasoning makes sense. If not let me know and I can try to explain a better way!

 

[nero6014]

Good morning, Thank You for your explanation. I'm still having problems understanding how the underlined part with the two close parenthesis works. Is this part simply saying if the two IF statements are false then do c2-b2? This is the first time I've seen it written that way and the parens are confusing me. Because of the parens I would think that C2) belongs to the first IF statement and B2) belongs to the second but logic says that there is no C2 in the first IF statement so does that mean it does not matter what order you put them in, Excel just knows which belongs to which. Probably sounds stupid but this was a great solution to my problem and if I fully understand it I can use it again. Also I don't see a way to mark your answer as the solution, how is that done? Thanks for taking more time than you should have to on this. =if(b2 = "" , 0,if(c2="", today(),c2)-b2)