" False" error message using isblank with 2 if statements

[Jim Chad]

IF(isblank(D12),(F17<365,F27,IF(F17>364,F37,f12)))

Can anyone tell me what is wrong with my formula?

 

[Jim Chad]

July 2nd, 2018

C10 is ='Start Here'!C10 which refers to [TABLE="width: 184"]
<tbody>[TR]
[TD="align: right"]04/03/18[/TD]
[/TR]
</tbody>[/TABLE]

c11 is ='Start Here'!C11 which refers to 5/1/2018

I think this is all of them. Thank you for all of your help.

 

[Joe4]

We are still missing values in a bunch of other cells, as you have formulas referencing C6:C9 on your Start Here sheet, and then we are missing D11, F22, and F32.
Or, simply let me know the results of the formulas in the cells I originally listed (instead of providing the underlying formulas if you are not going to give me the values in all the cells that those underlying formulas reference.

 

[Jim Chad]

We are still missing values in a bunch of other cells, as you have formulas referencing C6:C9 on your Start Here sheet, and then we are missing D11, F22, and F32.
Or, simply let me know the results of the formulas in the cells I originally listed (instead of providing the underlying formulas if you are not going to give me the values in all the cells that those underlying formulas reference.

on this work sheet
D6, d7,d8 and d9 are blank
D10 is 4/3/18
D11 is 5/1/18
D12 is 7/2/18


on the first worksheet c10 is 4/3/18
c11 is 5/1/18
c12 is 7/2/18

 

[Joe4]

Unfortunately, you still giving me all the information I asked for. I asked for:

you have formulas referencing C6:C9 on your Start Here sheet

and

D11, F22, and F32

and in your last post D11 was the only one of these 7 values you provided.

I am really trying to help you, but I cannot seem to get you to give me the information that I have asked for.
Can you please go back and read very carefully what it is I am asking for and provide that?

The only other alternative would be for you to post images of what your sheets look like (there are tools listed in Section B of the following that tell you how to do that: http://www.mrexcel.com/forum/board-a...forum-use.html), or for you to upload a copy of your file and provide a link to it. However, note that I would not be able to download any files until later tonight, as my company's security policy does not allow for me to download any files.

 

[Jim Chad]

Thank you for your patience.
On the start sheet:
C6, C7, C8, and C9 are blank

C10 HAS 4/3/2018
C11 HAS 5/1/2018
C12 HAS 7/2/2018

The rest are on the deferral worksheet
D6 has ='Start Here'!C6 - Since C6 on the start here sheet is blank this is giving me a zero
D7 has ='Start Here'!C7 Since C7 on the start here sheet is blank this is giving me a zero
D8 has ='Start Here'!C8 Since C8 on the start here sheet is blank this is giving me a zero
D9 has ='Start Here'!C9 Since C9 on the start here sheet is blank this is giving me a zero


the Deferral worksheet :
D11 ='Start Here'!C11
F22 =DATE(YEAR(D10),MONTH(D10)+2,1)
F32 =DATE(YEAR(D12),MONTH(D12)+2,1)

 

[Joe4]

OK, I think I have all the data now.
See if this gets you what you are after:

=IF(ISBLANK(D12),IF(F17<365,F27,F37),F12)

 

[Jim Chad]

OK, I think I have all the data now.
See if this gets you what you are after:

=IF(ISBLANK(D12),IF(F17<365,F27,F37),F12)

no error, but I get a blank whether f17 is < or > 365

 

[Joe4]

Is D12 blank?
If not, it will never look at F17.
The formula only looks at F17 if D12 is blank.

If that is not what you want, then you have an issue with the formula logic.
So then lets take a step back and forget about the formula, and just tells us (in plain English) what SHOULD happen in each scenario, i.e.
- D12 blank and F17 < 365
- D12 blank and F17 >= 365
- D12 NOT blank and F17 < 365
- D12 NOT blank and F17 >= 365

 

[Jim Chad]

Is D12 blank?
If not, it will never look at F17.
The formula only looks at F17 if D12 is blank.

If that is not what you want, then you have an issue with the formula logic.
So then lets take a step back and forget about the formula, and just tells us (in plain English) what SHOULD happen in each scenario, i.e.
- D12 blank and F17 < 365
- D12 blank and F17 >= 365
- D12 NOT blank and F17 < 365
- D12 NOT blank and F17 >= 365

- D12 blank and F17 < 365 F27 should be copied into this cell
- D12 blank and F17 >= 365 F37 should be copied into this cell
- D12 NOT blank and F17 < 365 D12 should be copied into this cell
- D12 NOT blank and F17 >= 365 D12 should be copied into this cell

And Thank You so much for all of the time you are spending on this.

 

[Joe4]

Ah, I think it was because you had a typo in your original formula (which is what I was working off of).
You had it returning F12, not D12. Here was your original formula:

[COLOR=#333333]IF(isblank(D12),(F17<365,F27,IF(F17>364,F37,[/COLOR][COLOR=#ff0000]f12[/COLOR][COLOR=#333333])))[/COLOR]

So if it is really D12 we want at the end and not F12, then simply change the last value in my last formula:

=IF(ISBLANK(D12),IF(F17<365,F27,F37),[COLOR=#ff0000]D12[/COLOR])