formula help with text "yes, "no" already have formula just need it revised

[steve89]

hello i have this formula

=IFERROR(IF($BQ$24=13,IF($BL$26=0,"yes","no")&TEXT($BL$26/BL$26,),"NO"),"yes")

i want the formula to know the following because it works to an extent
if BL26 = 0 then "NO"
if BQ24 = 13 then "YES"


thank you

 

[steve89]

Header are days of the month in numerical valve 1,2,3,4,5 etc. Months on cells B12:B23 are spelled out January, february etc

 

[dreid1011]

Hmm, I'm not sure how you got a value of 431321 then. Date values right now are only 5 digits long. And it sounds like you have them formatted the same way I did, and it worked for me. Do you get a better result if you wrap it in =DATEVALUE() ?

 

[steve89]

thanks for the reply can you submit the formula i tried putting date value and gave me an error this is what i did

=datevalue(B12:B23,SUMPRODUCT(--(C12:AG23="eg/ig")*ROW(C12:AG23))-11)&" "&INDEX(C11:AG11,,SUMPRODUCT(--(C12:AG23="eg/ig")*COLUMN(C12:AG23))-2)

 

[dreid1011]

Ok, formula provided in #17 wrapped in DATEVALUE():

=DATEVALUE(INDEX(B12:B23,SUMPRODUCT(--(C12:AG23="eg/ig")*ROW(C12:AG23))-11)&" "&INDEX(C11:AG11,,SUMPRODUCT(--(C12:AG23="eg/ig")*COLUMN(C12:AG23))-2))

I highlighted what I think you left out when wrapping the formula.

 

[steve89]

not sure whats happening so i attached a picture for more transparency of things - note where its says #value on cell U6 which reads "Date Evaluation Giving/Raise:"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
2							Title:				cook				EMPLOYEE ID:			F19038			4/27/2012				Hire Date:
3		SELECT THE YEAR					Status:				Full Time				PAYRATE:			$14.21							Termination Date:
4		2018					Name Badges Giving:				0				ACCOUNT TYPE:			NON-UNIONIZED			YES				Evaluation Giving
5		Employee Name					Total Uniforms Giving:				0				Amount of Increase:			$0.21			yes				Increase Giving:
6		SHEMEKIA ADAMS					Total Shoes Giving:				0				Percentage Increase:			1.5%			[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value[/URL] !				Date Evaluation Giving/Raise:
7		END DATE													Building & Payroll Code:			MILLH41 - 9YY
8		december
9
10
11			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
12	1	January
13	2	February	EG/IG																												NA	NA	NA
14	3	March
15	4	April																															NA
16	5	May
17	6	June																															NA
18	7	July
19	8	August
20	9	September																															NA
21	10	October
22	11	November																															NA
23	12	December
EMPLOYEE REPORT

 

[steve89]

formula you just provided is still asking for a value its weird

 

[dreid1011]

Oh.. try replacing "eg/ig" in the formula with "EG/IG".

 

[steve89]

revised formula with the "EG/IG" replacement - still asking for value...hmmm this is odd

=DATEVALUE(INDEX(B12:B23,SUMPRODUCT(--(C12:AG23="EG/IG")*ROW(C12:AG23))-11)&" "&INDEX(C11:AG11,,SUMPRODUCT(--(C12:AG23="EG/IG")*COLUMN(C12:AG23))-2))

 

[dreid1011]

Ok.. try entering the formula with CTRL+SHIFT+ENTER. I didn't think I entered it as an array, but never know.

 

[steve89]

tried the array as well didnt work still asking for value