Formula for Multiple IFs condition

[reza_doang]

Hi,

Please help your assistant for multiple ifs condition.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Due Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1/23/2018 3:58:13 PM[/TD]
[TD]1 - 2 weeks[/TD]
[/TR]
[TR]
[TD]2/13/2018 10:55:43 AM[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]1/29/2018 10:11:13 AM[/TD]
[TD]> 1 week[/TD]
[/TR]
[TR]
[TD]1/15/2018 3:58:13 PM[/TD]
[TD]2 - 3 weeks[/TD]
[/TR]
</tbody>[/TABLE]

i want to have formula in column status based on due date and today, sample like above

Please let me know if you need more details

thanks in advance

Reza

 

[jtakw]

Hi,

Wouldn't 1/29/2018 be < 1 week rather than > 1 week?

 

[reza_doang]

Hi,

Wouldn't 1/29/2018 be < 1 week rather than > 1 week?

Yes, that is correct, sorry for the typo

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]Due Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1/23/2018 3:58:13 PM[/TD]
[TD]1 - 2 weeks[/TD]
[/TR]
[TR]
[TD]2/13/2018 10:55:43 AM[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]1/29/2018 10:11:13 AM[/TD]
[TD]< 1 week[/TD]
[/TR]
[TR]
[TD]1/15/2018 3:58:13 PM[/TD]
[TD]2 - 3 weeks[/TD]
[/TR]
</tbody>[/TABLE]

thanks for the correction

 

[Legacy 143009]

Never mind. My account ahs a problem and I cant script down full formulas

 

[Legacy 143009]

I think this formula should work

 

[jtakw]

Hi,

No problem, but how far back might it go?
This will give you up to more than 3 weeks, but no specifics, you might want to consider # of Days as a result.

Book1
	A	B
1	Due Date
2	1/23/2018 15:58	1 - 2 weeks
3	2/13/2018 10:55	OK
4	1/29/2018 10:11	< 1 week
5	1/15/2018 15:58	2 - 3 weeks
6	12/1/2017 10:55	> 3 weeks
Sheet6

Cell Formulas
Range	Formula
B2	=IF(A2>=TODAY(),"OK",LOOKUP(DAYS(TODAY(),A2),{0,6,13,21},{"< 1 week","1 - 2 weeks","2 - 3 weeks","> 3 weeks"}))

B2 formula copied down.

 

[Legacy 143009]

=IF(AND(A2<TODAY(),WEEKNUM(A2,21)=WEEKNUM(TODAY(),21)),"< 1 week",IF(WEEKNUM(A2,21)<WEEKNUM(TODAY(),21),(WEEKNUM(TODAY(),21)-WEEKNUM(A2,21))&" - "&(WEEKNUM(TODAY(),21)-WEEKNUM(A2,21)+1)&" weeks",IF(A2>=TODAY(),"OK")))

 

[jtakw]

Oops, had a couple of typos, the 6 should be 7, and the 13 should be 14, this is corrected:

Book1
	A	B
1	Due Date
2	1/23/2018 15:58	1 - 2 weeks
3	2/13/2018 10:55	OK
4	1/29/2018 10:11	< 1 week
5	1/15/2018 15:58	2 - 3 weeks
6	12/1/2017 10:55	> 3 weeks
7	1/16/2018 15:58	2 - 3 weeks
8	1/9/2018 10:11	> 3 weeks
9	1/10/2018 15:58	2 - 3 weeks
10	1/8/2018 10:11	> 3 weeks
11	1/24/2018 10:55	< 1 week
12	1/17/2018 10:11	1 - 2 weeks
Sheet6

Cell Formulas
Range	Formula
B2	=IF(A2>=TODAY(),"OK",LOOKUP(DAYS(TODAY(),A2),{0,7,14,21},{"< 1 week","1 - 2 weeks","2 - 3 weeks","> 3 weeks"}))

 

[reza_doang]

Oops, had a couple of typos, the 6 should be 7, and the 13 should be 14, this is corrected:

	A	B
Due Date
1 - 2 weeks
OK
< 1 week
2 - 3 weeks
> 3 weeks
2 - 3 weeks
> 3 weeks
2 - 3 weeks
> 3 weeks
< 1 week
1 - 2 weeks

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/23/2018 15:58[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2/13/2018 10:55[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/29/2018 10:11[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/15/2018 15:58[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12/1/2017 10:55[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/16/2018 15:58[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/9/2018 10:11[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/10/2018 15:58[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/8/2018 10:11[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/24/2018 10:55[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/17/2018 10:11[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(A2>=TODAY(),"OK",LOOKUP(DAYS(TODAY(),A2),{0,7,14,21},{"< 1 week","1 - 2 weeks","2 - 3 weeks","> 3 weeks"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thanks Sir, this is working fine... i like this... thanks a ton

 

[jtakw]

You're welcome, glad it's working for you.