If condition help

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,284
Office Version
  1. 365
Platform
  1. Windows
Hi All,

after longtime on board..

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

Excel Formula: 
=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.
 
Sort by date Sort by votes
VBABEGINER said:
WINS file.xlsb
K
73585FTE
Day wise prod % (2)
Cell Formulas
RangeFormula
K73585K73585=IF(G75385="","", CHOOSE(IF(AND(O73585="Manila",C73585>=DATEVALUE("8/5/2023")), INT(I73585/5.001)+1, IF(INT(I73585/5.001)+1>16,28,INT(I73585/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"))
Click to expand...
Manila is ML
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
VBABEGINER said:
Hi, cant we keep date as per my format? if I used the date format as per your change then I need to change dates formats to almost all the sheets
Click to expand...
I thought the question was to get the IF formula correct. I used your EXACT cell references.

The date format is my default. You are more than welcome to use yours. and in the formula I use the Date Function. not a number with a format.
The format of the cell with the date in it should make no difference.
 
Upvote 0
pjmorris said:
A minor amendment:

Excel Formula: 
=IF(G75380="","",
CHOOSE(IF(AND(O73580="ML",C73580>=DATEVALUE("8/5/2023")), max(27,INT(I73580/5.001)+1), IF(INT(I73580/5.001)+1>16,28,INT(I73580/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"))

I haven't tested this as I've ditched the test spreadsheet. But the problem will have been caused by numbers above 135 evaluating to 28, this is corrected with the clause:
Excel Formula: 
max(27, INT(I73580/5.001)+1)
Click to expand...
Hi, can you please help me here..
 
Upvote 0
pjmorris said:
A minor amendment:

Excel Formula: 
=IF(G75380="","",
CHOOSE(IF(AND(O73580="ML",C73580>=DATEVALUE("8/5/2023")), max(27,INT(I73580/5.001)+1), IF(INT(I73580/5.001)+1>16,28,INT(I73580/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"))

I haven't tested this as I've ditched the test spreadsheet. But the problem will have been caused by numbers above 135 evaluating to 28, this is corrected with the clause:
Excel Formula: 
max(27, INT(I73580/5.001)+1)
Click to expand...
Hi pjmorris, can you please look, we're very close to solution. something needs to correct in your formula to get the correct solution
 
Upvote 0
WINS file.xlsb
CDEFGHIJKLMNO
1DateMonthActive StatusProdAID Processing DateCOI Processing DateAID DaysCOI DaysAID WeekCOI WeekAID TargetCOI TargetCenter
201/01/20231011/1/19001/1/19003209032090FTEFTE1.001.00Manila
301/02/20231011/1/19001/1/19003209132091FTEFTE1.001.00Manila
401/03/20231011/1/19001/1/19003209232092FTEFTE1.001.00Manila
501/04/20231011/1/19001/1/19003209332093FTEFTE1.001.00Manila
601/05/20231011/1/19001/1/19003209432094FTEFTE1.001.00Manila
701/06/20231011/1/19001/1/19003209532095FTEFTE1.001.00Manila
801/07/20231011/1/19001/1/19003209532095FTEFTE1.001.00Manila
901/08/20231011/1/19001/1/19003209532095FTEFTE1.001.00Manila
1001/09/20231011/1/19001/1/19003209632096FTEFTE1.001.00Manila
1101/10/20231011/1/19001/1/19003209732097FTEFTE1.001.00Manila
1201/11/20231011/1/19001/1/19003209832098FTEFTE1.001.00Manila
1301/12/20231011/1/19001/1/19003209932099FTEFTE1.001.00Manila
1401/13/20231011/1/19001/1/19003210032100FTEFTE1.001.00Manila
1501/14/20231011/1/19001/1/19003210032100FTEFTE1.001.00Manila
1601/15/20231011/1/19001/1/19003210032100FTEFTE1.001.00Manila
1701/16/20231011/1/19001/1/19003210132101FTEFTE1.001.00Manila
1801/17/20231011/1/19001/1/19003210232102FTEFTE1.001.00Manila
1901/18/20231011/1/19001/1/19003210332103FTEFTE1.001.00Manila
Day wise prod % (2)
Cell Formulas
RangeFormula
I2:I19I2=NETWORKDAYS(G2,C2)
J2:J19J2=IF(OR(H2>C2,H2=""),"",NETWORKDAYS(H2,C2))
K2:K19K2=IF(G2="","", CHOOSE( IF(AND(O2="ML",C2>=DATEVALUE("8/5/2023")), 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:L19L2=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:M19M2=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:N19N2=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))))))))))
 
Upvote 0
Hi awoohaw, pjmorris, fluff and all,

here is my sample sheet of data. what I want is wherever it is Manila and date greater than 8th May, I need below condition to work
Excel Formula: 
IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(AND(I73580>5,I73580<=10),"Week 2",
IF(AND(I73580>10,I73580<=20),"Week 3 & 4",
IF(AND(I73580>20,I73580<=30),"Week 5 & 6",
IF(AND(I73580>30,I73580<=40),"Week 7 & 8",
IF(AND(I73580>40,I73580<=50),"Week 9 & 10",
IF(AND(I73580>50,I73580<=60),"Week 11 & 12",
IF(AND(I73580>60,I73580<=70),"Week 13 & 14",
IF(AND(I73580>70,I73580<=80),"Week 15 & 16",
IF(AND(I73580>80,I73580<=85),"Week 17",
IF(AND(I73580>85,I73580<=100),"Week 18 & 20",
IF(AND(I73580>100,I73580<=115),"Week 21 & 23",
IF(AND(I73580>115,I73580<=130),"Week 24 & 26",
IF(I73580>130,"Week 27 onwards",

if anyone condition is not true then
Excel Formula: 
IF(G73580="","",
IF(I73580<=5,"Week 1",
IF(AND(I73580>5,I73580<=10),"Week 2",
IF(AND(I73580>10,I73580<=20),"Week 3 & 4",
IF(AND(I73580>20,I73580<=30),"Week 5 & 6",
IF(AND(I73580>30,I73580<=40),"Week 7 & 8",
IF(AND(I73580>40,I73580<=50),"Week 9 & 10",
IF(AND(I73580>50,I73580<=60),"Week 11 & 12",
IF(AND(I73580>60,I73580<=70),"Week 13 & 14",
IF(AND(I73580>70,I73580<=80),"Week 15 & 16","FTE"))))))))))))))))))))))))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Revenue Allocation by Week
Replies
3
Views
805
ChetanPuri
C
T
  • Solved
numbers and dates....
Replies
5
Views
438
awoohaw
awoohaw
C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
4K
Fluff
Fluff
S
  • Question Question
2 Tab Data Comparison & Update Help
Replies
1
Views
330
Sunshine8790
S
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
707
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,224,616
Messages
6,179,910
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top