IF Statement

[cmorales66]

Hi everyone. I know I may be missing a rookie link here, I'm just burned out at this point. Help.

I'd like to name my Processed Count column based on the Completed Column. If there is no date in Completed, then the Days are assumed Open: Open 7 Days or Less, Open 8-14 Days, etc. If there's a date on Completed, then Days are assumed processed: Processed in 7 Days or Less, Processed in 8-14 Days, etc. I have the formula below and it works with the Completed column empty but if there's a value, it returns FALSE. I'm missing that end piece to make it work. Any help is appreciated.

My current formula: =IF([@Completed]="",IF([@Days]<=7,"Open 7 Days or Less",IF([@Days]<=14,"Open 8-14 Days",IF([@Days]<=21,"Open 15-21 Days",IF([@Days]>21,"Open over 21 Days",IF([@Completed]<>"",IF([@Days]<=7,"Processed in 7 Days or Less",IF([@Days]<=14,"Processed in 8-14 Days",IF([@Days]<=21,"Processed in 15-21 Days",IF([@Days]>21,"Processed in over 21"))))))))))

 

[Elaszat]

Hi cmorales66,

It works like this:

Book1
	A	B	C
1	Completed	Days	Processed Count
2		7	Open 7 Days or Less
3	09.01.2020	35	Processed in over 21 Days
4		Open	Open over 21 Days
5	05.12.2019	9	Processed in 8 - 14 Days
6	29.11.2019	8	Processed in 8 - 14 Days
7	21.12.2019	30	Processed in over 21 Days
Tabelle1

Cell Formulas
Range	Formula
C2:C7	C2	=IF([@Completed]="",IF([@Days]<=7,"Open 7 Days or Less",IF([@Days]<=14,"Open 8 - 14 Days",IF([@Days]<=21,"Open 14 to 21 Days","Open over 21 Days"))),IF([@Days]<=7,"Processed in 7 Days or Less",IF([@Days]<=14,"Processed in 8 - 14 Days",IF([@Days]<=21,"Processed in 14 to 21 Days","Processed in over 21 Days"))))

You have two If-Formulas nested in the outer formula, but the inner two should not be nested into each other, because the first one is the True-Statement of the outer If-Formula, the other is the False-Statement.

Regards,
Elaszat

 

[FormR]

Hi, just another option that you can try.

Book1
	A	B	C
1	Completed	Days	Processed Count
2		7	Open 7 Days or less
3	09.01.2020	35	Processed over 21 Days
4		15	Open 15-21 Days
5	05.12.2019	21	Processed 15-21 Days
6	29.11.2019	8	Processed 8-14 Days
7	21.12.2019	30	Processed over 21 Days
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF([@Completed]="","Open ","Processed ")&LOOKUP([@Days],{0,8,15,22},{"7 Days or less","8-14 Days","15-21 Days","over 21 Days"})

 

[Elaszat]

That looks nice, very elegant.

 

[cmorales66]

Hi cmorales66,

It works like this:

Book1
	A	B	C
1	Completed	Days	Processed Count
2		7	Open 7 Days or Less
3	09.01.2020	35	Processed in over 21 Days
4		Open	Open over 21 Days
5	05.12.2019	9	Processed in 8 - 14 Days
6	29.11.2019	8	Processed in 8 - 14 Days
7	21.12.2019	30	Processed in over 21 Days
Tabelle1

Cell Formulas
Range	Formula
C2:C7	C2	=IF([@Completed]="",IF([@Days]<=7,"Open 7 Days or Less",IF([@Days]<=14,"Open 8 - 14 Days",IF([@Days]<=21,"Open 14 to 21 Days","Open over 21 Days"))),IF([@Days]<=7,"Processed in 7 Days or Less",IF([@Days]<=14,"Processed in 8 - 14 Days",IF([@Days]<=21,"Processed in 14 to 21 Days","Processed in over 21 Days"))))

You have two If-Formulas nested in the outer formula, but the inner two should not be nested into each other, because the first one is the True-Statement of the outer If-Formula, the other is the False-Statement.

Regards,
Elaszat

Thank you for this! I was getting overwhelmed with the statements. This is a very good solution to keep in mind when using two nested formulas but one in the outside. Thank you again for your help.

 

[cmorales66]

Hi, just another option that you can try.

Book1
	A	B	C
1	Completed	Days	Processed Count
2		7	Open 7 Days or less
3	09.01.2020	35	Processed over 21 Days
4		15	Open 15-21 Days
5	05.12.2019	21	Processed 15-21 Days
6	29.11.2019	8	Processed 8-14 Days
7	21.12.2019	30	Processed over 21 Days
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF([@Completed]="","Open ","Processed ")&LOOKUP([@Days],{0,8,15,22},{"7 Days or less","8-14 Days","15-21 Days","over 21 Days"})

This is like a German car of a reply, efficient, elegant and it works! Thank you for your help, this is a very clean solution.

 

[Elaszat]

Thank you for this! I was getting overwhelmed with the statements. This is a very good solution to keep in mind when using two nested formulas but one in the outside. Thank you again for your help.

You're welcome and thanks for the feedback!