If statement using istext and isnumber

[itr674]

Trying to run a simple IF/AND/ISTEXT/ISNUMBER but can't get it, probably simple fix...

Using this formula in range C3:C5

=IF(AND(ISTEXT(A3),ISTEXT(B3)),1,
IF(AND(ISNUMBER(A3),ISTEXT(B3),A3+$B$2>=$B$1),0,
IF(AND(ISTEXT(A3),ISNUMBER(B3),B3+$B$2>=$B$1),0,1)))

[TABLE="width: 242"]
<tbody>[TR]
[TD="class: xl67, width: 109, bgcolor: transparent"]Target
[/TD]
[TD="class: xl68, width: 116, bgcolor: transparent"]1-Sep-18
[/TD]
[TD="class: xl67, width: 97, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Freq (days)
[/TD]
[TD="class: xl66, bgcolor: transparent"]360
[/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Text
[/TD]
[TD="class: xl65, bgcolor: transparent"]Text
[/TD]
[TD="class: xl66, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]28-Sep-17
[/TD]
[TD="class: xl65, bgcolor: transparent"]Text
[/TD]
[TD="class: xl66, bgcolor: transparent"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Text
[/TD]
[TD="class: xl65, bgcolor: transparent"]4-Oct-17
[/TD]
[TD="class: xl66, bgcolor: transparent"]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Is A3 a number?

 

[itr674]

A3 and B3 would be TEXT
A4 is a number and B4 is TEXT
A5 is TEXT and B5 is a number

and columns A and B alternate that way for the entire sheet...I just cant figure out how to get rid the #VALUE ...

the TEXT is normally something like "item not started", "not complete" and so one.
the NUMBER is a date.

The reason for two columns is you can take the entire course, or "pre-test" out of the course...

 

[jtakw]

Hi,

See if this does what you need:

Book1
	A	B	E
1	Target	1-Sep-18
2	Freq (days)	360
3	Text	Text	1
4	28-Sep-17	Text	0
5	Text	4-Oct-17	0
Sheet22

Cell Formulas
Range	Formula
E3	=IF(ISNUMBER(A3),IF(A3+$B$2>=$B$1,0,1),IF(ISNUMBER(B3),IF(B3+$B$2>=$B$1,0,1),1))

 

[itr674]

jtakw, perfecto!!!, thank you!!!

 

[jtakw]

jtakw, perfecto!!!, thank you!!!

You're welcome.