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

 

[dreid1011]

Hmm, if you wanted to send me a copy of the file, I could look at it this evening. Until then, all I can do is attempt to recreate the data, but I don't think that will do as it seems there is some unknown that is keeping it from returning the expected value.

 

[steve89]

i dont mind sending the file or trying to recreate the data i just sincerely appreciate the help

 

[dreid1011]

Ok. I know I asked this already, but one more time.

Tell me how exactly the Month names and Month days are formatted, the formatting chosen in the drop down (general, text, number, etc), and the formatting of the range C12:AG23. I will try to recreate the table again and see what happens.

 

[steve89]

sure no problem

B8 = you choose your year
Cells B12:B23 = =DATE($B$4,ROW(B12)-ROW($B$11),1)
B11:AG23= number days so 1,2,3,4,5,6,7,8,9,10 - 31 of month
on a separate page you enter a start date and end date of a conditon this case the condition is "EG/IG" which can only appear once a year on the calendar i provided between cells C12:AG23
The month appear spelled out January etc but on Cells A12:A23 the number of the months are 1=january etc

 

[dreid1011]

So, is EG/IG being populated from a formula, or manually entered on the calendar?

Ok, scratch that question. Is it a cell format to display just the month name in B12:B23?

 

[dreid1011]

Ok, one last try:

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

 

[steve89]

unbelievable wow thank you so much this worked perfectlyyyyyyyyyy - your a god lol thanks so much

 

[dreid1011]

You're quite welcome. Glad I could help.

 

[steve89]

after everything you have helped me with i wanted to see if one more thing was possible i've developed this vacation accrue formula for employees but doesnt work the way id like it to. i would like for when an employee is hired that after a year the accrued amount would be added automatically based off todays date. lets say 1/1/2018 hired date 1/1/2019 would be 5 days accrued this is how i have it see below

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3	EMPLOYEE NAME	START DATE	TERMINATION DATE	Title	Anniversary	Months Since Anniversary	Status	EMPLOYEE ID	PAY RATE	Vacation	Sick	Personal
4	SHEMEKIA ADAMS	4/27/2012		cook	4/27/2018	0	Full Time	F19038	$14.00	0	0	0
5	GARNETT MCMULLEN	6/3/2014		cook	6/3/2018	0	Full Time	K19043	$12.00	0	0	0
EMPLOYEES

Cell Formulas
Range	Formula
F4	=DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))
F5	=DATE(YEAR(TODAY()),MONTH(C5),DAY(C5))
G4	=IFERROR(DATEDIF(F4,'E:\Accounts\Millhouse\Staffing\Attendance & PTO Sheets\Temp. Projects\[2018 Attendance & Infraction Log-- MASTER NEW.xlsm]Formula Data Sheet'!$K$45,"m"),0)
G5	=IFERROR(DATEDIF(F5,'E:\Accounts\Millhouse\Staffing\Attendance & PTO Sheets\Temp. Projects\[2018 Attendance & Infraction Log-- MASTER NEW.xlsm]Formula Data Sheet'!$K$45,"m"),0)
K4	=IFERROR(LOOKUP($E4,'PTO Hours Settings'!$A$5:$B$9,FALSE)/12*G4,0)
K5	=IFERROR(LOOKUP($E5,'PTO Hours Settings'!$A$5:$B$9,FALSE)/12*G5,0)
L4	=IFERROR(VLOOKUP($E4,'PTO Hours Settings'!$D$5:$E$9,FALSE)/12*G4,0)
L5	=IFERROR(VLOOKUP($E5,'PTO Hours Settings'!$D$5:$E$9,FALSE)/12*G5,0)
M4	=IFERROR(VLOOKUP($E4,'PTO Hours Settings'!$G$5:$H$9,FALSE)/12*G4,0)
M5	=IFERROR(VLOOKUP($E5,'PTO Hours Settings'!$G$5:$H$9,FALSE)/12*G5,0)

Named Ranges
Name	Refers To	Cells
M	='Formula Data Sheet'!$B$10:$B$79
year	='Daily Schedule'!$J$4

Book1
	A	B	C	D	E	F	G	H
4	Vacation Accrue Amounts for 1 year			Sick Day Accrue Amounts for 1 year			Personal Day Accrue Amounts for 1 year
5	Food Service Director	5.00		Food Service Director	0.00		Food Service Director	0.00
6	Assistant Manager	5.00		Assistant Manager	0.00		Assistant Manager	0.00
7	Cook	5.00		Cook	6.00		Cook	0.00
8	Dietary Aide	5.00		Dietary Aide	6.00		Dietary Aide	0.00
9	Receiver/Utility	5.00		Receiver/Utility	6.00		Receiver/Utility	0.00
10
11	Vacation Accrue Amounts for 2 years or more			Sick Day Accrue Amounts for 2 years or more			Personal Day Accrue Amounts for 2 years or more
12	Food Service Director	5.00		Food Service Director	0.00		Food Service Director	0.00
13	Assistant Manager	5.00		Assistant Manager	0.00		Assistant Manager	0.00
14	Cook	10.00		Cook	8.00		Cook	0.00
15	Dietary Aide	10.00		Dietary Aide	8.00		Dietary Aide	0.00
16	Receiver/Utility	10.00		Receiver/Utility	8.00		Receiver/Utility	0.00
PTO Hours Settings

 

[steve89]

so pretty much what im looking for is the formula in K4 to know that based off of sheet "PTO HOURS SETTINGS" whatever is entered needs to apply after the employees anniversary year. (which is what a manager would enter per account and would vary)