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
pjmorris said:
hi, a somewhat more complex formula, but it appears to solve the problem:

Excel Formula: 
=IF(G75380="","",
CHOOSE(IF(AND(O73580="ML",C73580>=DATEVALUE("8/5/2023")), 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"))

The criteria elements are in the first part of the formula and the latter is simply a list of the responses.

HTH
Click to expand...
Hi pjmorris,

Thank You for your reply..
Your logic is Excellent.
The only problem is, if when days in Col I goes 136 that time it capture "FTE" instead of "Week 27 Onwards" if condition is ML and date next to 8th May. Till 135 its giving me "Week 27 Onwards" only..
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How does this work:

mr excel questions 34.xlsm
GHIJK
73579TEXTNUMBER
73580MLA0Week 1
73581AXC2Week 1
73582VZJ5Week 1
73583VPZ7Week 2
73584QQH10Week 2
73585DNB12Week 3 & 4
73586SBR15Week 3 & 4
73587DRU17Week 3 & 4
73588IHE20Week 3 & 4
73589SXJ22Week 5 & 6
73590KJS25Week 5 & 6
73591UPM27Week 5 & 6
73592YIO30Week 5 & 6
73593PYT32Week 7 & 8
73594RDD35Week 7 & 8
73595EUG37Week 7 & 8
73596AJC40Week 7 & 8
73597HRW42Week 9 & 10
73598VOQ45Week 9 & 10
73599FQT47Week 9 & 10
73600KDZ50Week 9 & 10
73601MRO52Week 11 & 12
73602BVG55Week 11 & 12
73603VAO57Week 11 & 12
73604HVQ60Week 11 & 12
73605QIH62Week 13 & 14
73606BHA65Week 13 & 14
73607KDN67Week 13 & 14
73608YGP70Week 13 & 14
73609KVD72Week 15 & 16
73610USG75Week 15 & 16
73611XFU77Week 15 & 16
73612RSE80Week 15 & 16
73613AAT82FTE
73614RQB85FTE
73615PUZ87FTE
VBABEGINNER
Cell Formulas
RangeFormula
K73580:K73615K73580= 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"))))))))))
 
Upvote 0
sorry the above did not include a "" condition, here is smaller version of above with that:
mr excel questions 34.xlsm
GHIJK
73579TEXTNUMBER
73580MLA0Week 1
7358150 
VBABEGINNER
Cell Formulas
RangeFormula
K73580:K73581K73580= 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"))))))))))
 
Upvote 0
oops. I missed the entire first part. I think I have all the IFs in here now:

mr excel questions 34.xlsm
CGHIJKLMNO
73579DATETEXTNUMBERTEAM NAME
735802023-05-08MLA0Week 1ML
7358150 
73582AXC2Week 1
73583VZJ5Week 1
73584VPZ7Week 2
73585QQH10Week 2ML
735862023-05-09DNB12Week 3 & 4ML
73587SBR15Week 3 & 4ML
73588DRU17Week 3 & 4ML
73589IHE20Week 3 & 4ML
73590SXJ22Week 5 & 6ML
73591KJS25Week 5 & 6ML
73592UPM27Week 5 & 6ML
73593YIO30Week 5 & 6ML
73594PYT32Week 7 & 8ML
73595RDD35Week 7 & 8ML
73596EUG37Week 7 & 8ML
73597AJC40Week 7 & 8ML
73598HRW42Week 9 & 10ML
73599VOQ45Week 9 & 10ML
73600FQT47Week 9 & 10ML
73601KDZ50Week 9 & 10ML
73602MRO52Week 11 & 12
73603BVG55Week 11 & 12
73604VAO57Week 11 & 12
73605HVQ60Week 11 & 12
73606QIH62Week 13 & 14
73607BHA65Week 13 & 14
73608KDN67Week 13 & 14
73609YGP70Week 13 & 14
73610KVD72Week 15 & 16
73611USG75Week 15 & 16
73612XFU77Week 15 & 16
73613RSE80Week 15 & 16
73614AAT82FTE
73615RQB85FTE
73616PUZ87FTE
VBABEGINNER
Cell Formulas
RangeFormula
C73580C73580=DATE(2023,5,8)
C73586C73586=DATE(2023,5,9)
K73580:K73616K73580=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" )))))))))))))))
 
Upvote 0
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"))
 
Upvote 0
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)
 
Upvote 0
awoohaw said:
oops. I missed the entire first part. I think I have all the IFs in here now:
.....
Click to expand...
small correction, I missed an equal sign in the date condition check:

Excel Formula: 
=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" )))))))))))))))
 
Upvote 0
awoohaw said:
oops. I missed the entire first part. I think I have all the IFs in here now:

mr excel questions 34.xlsm
CGHIJKLMNO
73579DATETEXTNUMBERTEAM NAME
735802023-05-08MLA0Week 1ML
7358150 
73582AXC2Week 1
73583VZJ5Week 1
73584VPZ7Week 2
73585QQH10Week 2ML
735862023-05-09DNB12Week 3 & 4ML
73587SBR15Week 3 & 4ML
73588DRU17Week 3 & 4ML
73589IHE20Week 3 & 4ML
73590SXJ22Week 5 & 6ML
73591KJS25Week 5 & 6ML
73592UPM27Week 5 & 6ML
73593YIO30Week 5 & 6ML
73594PYT32Week 7 & 8ML
73595RDD35Week 7 & 8ML
73596EUG37Week 7 & 8ML
73597AJC40Week 7 & 8ML
73598HRW42Week 9 & 10ML
73599VOQ45Week 9 & 10ML
73600FQT47Week 9 & 10ML
73601KDZ50Week 9 & 10ML
73602MRO52Week 11 & 12
73603BVG55Week 11 & 12
73604VAO57Week 11 & 12
73605HVQ60Week 11 & 12
73606QIH62Week 13 & 14
73607BHA65Week 13 & 14
73608KDN67Week 13 & 14
73609YGP70Week 13 & 14
73610KVD72Week 15 & 16
73611USG75Week 15 & 16
73612XFU77Week 15 & 16
73613RSE80Week 15 & 16
73614AAT82FTE
73615RQB85FTE
73616PUZ87FTE
VBABEGINNER
Cell Formulas
RangeFormula
C73580C73580=DATE(2023,5,8)
C73586C73586=DATE(2023,5,9)
K73580:K73616K73580=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" )))))))))))))))
Click to expand...
Thank You for your reply. I appreciate time invested on my query.. I'm checking another solution.. Let me try this too and will get back to you..
 
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, Thanks for your reply. I appreciate your time investing in this query. I think the updated one slight not work well. Your first solution post more close to solution. What is not clearing in that solution is as mentioned earlier. Col O = ML and date above 8th May then if days is greater than 130 then "Week 27 onwards" else it should work my 2nd iteration.. Pls let me know if I'm not able to clear
 
Upvote 0
awoohaw said:
small correction, I missed an equal sign in the date condition check:

Excel Formula: 
=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" )))))))))))))))
Click to expand...
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
 
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