If condition help

[VBABEGINER]

Hi All,

after longtime on board..

I need one help in formula. date mm/dd/yyyy

=IF(AND(O1="ML",C1>=8/5/2023) then inner IF formulation should work (i.e. 1st iteration) else if any one condition not true then perform other IF formulation  i.e. (2nd iteration)

How do I achieve the same. Now with my current formula, what happens is, if ML is with less that 8th May date even then it work 1st iteration. Ideally it should give me what return in 2nd iteration. Pls guide..

Infact, even if my Col O and Col C have different values it is giving me False as o/p.

 

[awoohaw]

post no 15

that post is just once cell with the formula. there is no supporting data.
But it seems you have found a solution. That is great!

Best wishes.

 

[VBABEGINER]

that post is just once cell with the formula. there is no supporting data.
But it seems you have found a solution. That is great!

Best wishes.

Thank you for your reply. I dont find any solution. The post which pjmorris posted that close to what I want, but need correction. Hence waiting reply from him

 

[awoohaw]

Thank you for your reply. I dont find any solution. The post which pjmorris posted that close to what I want, but need correction. Hence waiting reply from him

what I offered was not good? Just paste this in a cell on row 73580
This should read your date format in column C as you have it.

=IF(AND(O73580="ML",C73580>=DATE(2023,5,8))=FALSE,
IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(I73580<=10,"Week 2",
IF(I73580<=20,"Week 3 & 4",
IF(I73580<=30,"Week 5 & 6",
IF(I73580<=40,"Week 7 & 8",
IF(I73580<=50,"Week 9 & 10",
IF(I73580<=60,"Week 11 & 12",
IF(I73580<=70,"Week 13 & 14",
IF(I73580<=80,"Week 15 & 16","FTE")))))))))),

IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(I73580<=10,"Week 2",
IF(I73580<=20,"Week 3 & 4",
IF(I73580<=30,"Week 5 & 6",
IF(I73580<=40,"Week 7 & 8",
IF(I73580<=50,"Week 9 & 10",
IF(I73580<=60,"Week 11 & 12",
IF(I73580<=70,"Week 13 & 14",
IF(I73580<=80,"Week 15 & 16",
IF(I73580<=85,"Week 17",
IF(I73580<=100,"Week 18 & 20",
IF(I73580<=115,"Week 21 & 23",
IF(I73580<=130,"Week 24 & 26","Week 27 onwards" )))))))))))))))

 

[VBABEGINER]

what I offered was not good? Just paste this in a cell on row 73580
This should read your date format in column C as you have it.

=IF(AND(O73580="ML",C73580>=DATE(2023,5,8))=FALSE,
IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(I73580<=10,"Week 2",
IF(I73580<=20,"Week 3 & 4",
IF(I73580<=30,"Week 5 & 6",
IF(I73580<=40,"Week 7 & 8",
IF(I73580<=50,"Week 9 & 10",
IF(I73580<=60,"Week 11 & 12",
IF(I73580<=70,"Week 13 & 14",
IF(I73580<=80,"Week 15 & 16","FTE")))))))))),

IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(I73580<=10,"Week 2",
IF(I73580<=20,"Week 3 & 4",
IF(I73580<=30,"Week 5 & 6",
IF(I73580<=40,"Week 7 & 8",
IF(I73580<=50,"Week 9 & 10",
IF(I73580<=60,"Week 11 & 12",
IF(I73580<=70,"Week 13 & 14",
IF(I73580<=80,"Week 15 & 16",
IF(I73580<=85,"Week 17",
IF(I73580<=100,"Week 18 & 20",
IF(I73580<=115,"Week 21 & 23",
IF(I73580<=130,"Week 24 & 26","Week 27 onwards" )))))))))))))))

HI, no not at all.. Infact i really appreciate your time and response on someone's query. You solution is for me to implement but in my col C I've date format like "mm/dd/yyyy" and in your solution you used different format. So I guess I need to change col C dates format as well if I use your solution.. hence I'm not going with that..

 

[awoohaw]

i'm not sure what you mean. It may have had different format in my worksheet on my system. But, if you had pasted the formula from POST #33 and not the xl2bb entirely it should have used only your worksheet formats.

The DATE(2023,5,8) is a function where the input order must always be YYYY,MM,DD. I'm not sure if your version of excel uses semi-colons as argument delimiters or not or even has a different name for the function.

 

[pjmorris]

Hi,

Apologies for delay in replying - I've been away.

Below is the test spreadsheet based on a previous post. Please note that I had to insert dates in column G otherwise nothing worked. My formula's are in column K, and I had to correct my previous amendment from MAX to MIN in:

=IF(G2="","",
CHOOSE(IF(AND(O2="ML",C2>=DATEVALUE("8/5/2023")), MIN(27,INT(I2/5.001)+1), IF(INT(I2/5.001)+1>16,28,INT(I2/5.001) +1)),
"Week 1","Week 2", "Week 3 & 4", "Week 3 & 4", "Week 5 & 6", "Week 5 & 6", "Week 7 & 8", "Week 7 & 8", "Week 9 & 10", "Week 9 & 10", "Week 11 & 12", "Week 11 & 12","Week 13 & 14", "Week 13 & 14","Week 15 & 16", "Week 15 & 16","Week 17", "Week 18 & 20","Week 18 & 20", "Week 18 & 20","Week 21 & 23", "Week 21 & 23","Week 21 & 23", "Week 24 & 26","Week 24 & 26", "Week 24 & 26","Week 27 Onwards","FTE"))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Date	Month	Active Status	Prod	AID Processing Date	COI Processing Date	AID Days	COI Days	AID Week	COI Week	AID Target	COI Target	Center
2			01/01/2023	1	0	1	01-Jan-23	1	0	32090	Week 1	FTE	0.25	1	ML
3			01/02/2023	1	0	1	02-Jan-23	1	23	32113	Week 5 & 6	FTE	0.5	1	ML
4			01/03/2023	1	0	1	03-Jan-23	1	42	32133	Week 9 & 10	FTE	0.7	1	ML
5			01/04/2023	1	0	1	04-Jan-23	1	63	32155	Week 13 & 14	FTE	0.9	1	ML
6			01/05/2023	1	0	1	05-Jan-23	1	83	32176	FTE	FTE	1	1	ML
7			01/06/2023	1	0	1	06-Jan-23	1	105	32199	Week 21 & 23	FTE	1	1	ML
8			01/07/2023	1	0	1	07-Jan-23	1	125	32220	Week 24 & 26	FTE	1	1	ML
9			01/08/2023	1	0	1	08-Jan-23	1	147	32242	Week 27 Onwards	FTE	1	1	ML
10			01/09/2023	1	0	1	09-Jan-23	1	170	32265	Week 27 Onwards	FTE	1	1	ML
11			01/10/2023	1	0	1	10-Jan-23	1	189	32285	Week 27 Onwards	FTE	1	1	ML
12			01/11/2023	1	0	1	11-Jan-23	1	211	32308	Week 27 Onwards	FTE	1	1	ML
13			01/12/2023	1	0	1	12-Jan-23	1	232	32330	Week 27 Onwards	FTE	1	1	ML
14			01/01/2024	1	0	1	13-Jan-23	1	252	32351	Week 27 Onwards	FTE	1	1	ML
15			01/02/2024	1	0	1	14-Jan-23	1	274	32374	Week 27 Onwards	FTE	1	1	ML
16			01/03/2024	1	0	1	15-Jan-23	1	295	32395	Week 27 Onwards	FTE	1	1	ML
17			01/04/2024	1	0	1	16-Jan-23	1	316	32416	Week 27 Onwards	FTE	1	1	ML
18			01/05/2024	1	0	1	17-Jan-23	1	337	32438	Week 27 Onwards	FTE	1	1	ML
19			01/06/2024	1	0	1	18-Jan-23	1	358	32460	Week 27 Onwards	FTE	1	1	ML
Sheet1

Cell Formulas
Range	Formula
I2:I19	I2	=NETWORKDAYS(G2,C2)
J2:J19	J2	=IF(OR(H2>C2,H2=""),"",NETWORKDAYS(H2,C2))
K2:K19	K2	=IF(G2="","", CHOOSE(IF(AND(O2="ML",C2>=DATEVALUE("8/5/2023")), MIN(27,INT(I2/5.001)+1), IF(INT(I2/5.001)+1>16,28,INT(I2/5.001) +1)), "Week 1","Week 2", "Week 3 & 4", "Week 3 & 4", "Week 5 & 6", "Week 5 & 6", "Week 7 & 8", "Week 7 & 8", "Week 9 & 10", "Week 9 & 10", "Week 11 & 12", "Week 11 & 12","Week 13 & 14", "Week 13 & 14","Week 15 & 16", "Week 15 & 16","Week 17", "Week 18 & 20","Week 18 & 20", "Week 18 & 20","Week 21 & 23", "Week 21 & 23","Week 21 & 23", "Week 24 & 26","Week 24 & 26", "Week 24 & 26","Week 27 Onwards","FTE"))
L2:L19	L2	=IF(H2="","",IF(J2<=10,"Week 1 & 2",IF(AND(J2>10,J2<=20),"Week 3 & 4",IF(AND(J2>20,J2<=30),"Week 5 & 6",IF(AND(J2>30,J2<=40),"Week 7 & 8",IF(AND(J2>=40,J2<=55),"Week 9 & 12",IF(AND(J2>55,J2<=70),"Week 13 & 15",IF(AND(J2>70,J2<=85),"Week 16 & 18",IF(AND(J2>85,J2<=100),"Week 19 & 21",IF(AND(J2>100,J2<=115),"Week 22 & 24","FTE"))))))))))
M2:M19	M2	=IF(G2="","",IF(K2="Week 1",0.25,IF(K2="Week 2",0.3,IF(K2="Week 3 & 4",0.4,IF(K2="Week 5 & 6",0.5,IF(K2="Week 7 & 8",0.6,IF(K2="Week 9 & 10",0.7,IF(K2="Week 11 & 12",0.8,IF(K2="Week 13 & 14",0.9,IF(K2="Week 15 & 16",1,1))))))))))
N2:N19	N2	=IF(H2="","",IF(L2="Week 1 & 2",0.2,IF(L2="Week 3 & 4",0.3,IF(L2="Week 5 & 6",0.4,IF(L2="Week 7 & 8",0.5,IF(L2="Week 9 & 12",0.6,IF(L2="Week 13 & 15",0.7,IF(L2="Week 16 & 18",0.8,IF(L2="Week 19 & 21",0.9,IF(L2="Week 22 & 24",1,1))))))))))

Hope that solves the problem.

Regards

 

[awoohaw]

why are you now in row 1? all the work I had done was for Row 73580 ? Did you adjust row number accordingly?
what is the error you have in Post #36? What cell(s) is/are wrong? and what are the expected values?

 

[pjmorris]

I posted it in row 1 because the OP did so in his sample at whatever post it was. Its quite confusing. The error was having a MAX term in my previous post when I should have had MIN as i needed to limit the range of choice to 27 in certain circumstances.