Nested If with dates & multiple day variables

Charger_R_T · Sep 11, 2023

I'm looking to place this formula in the status colume. I'll use row A15 as my example.
If A15 is blank, C15=blank
IF A15 has text, but no dates entered through E15:AM15, AO:AP, and AR:AT "Incomplete"
If E15:AM15, AO:AP, and AR:AT has a date over 365 days prior to today or If AL15:AM15 and AR15:AS15, has a date over 1095 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If E15:AM15, AO:AP, and AR:AT has a date between 335-365 days prior to today or If AL15:AM15 and AR15:AS15 has a date between 1065-1095 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IF E15:AM15, AO:AP, and AR:AT has all dates within 335 days prior to today and IF AL15:AM15 and AR15:AS15 has all dates within 1065, "Safety Qualified"

Row AN, AQ, AS will not be part of the formula.

Charger_R_T · Sep 11, 2023

Corrected some language from my original post. Sorry for the confusion.

If A15 is blank, C15=blank
IF A15 has text, but no dates entered through E15:AM15, AO15:AP15, and AR15:AT15 "Incomplete"
If E15:AM15, AO15:AP15, and AR15:AT15 has a date over 365 days prior to today or If AL15:AM15 and AR15:AS15, has a date over 1095 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If E15:AM15, AO15:AP15, and AR15:AT15 has a date between 335-365 days prior to today or If AL15:AM15 and AR15:AS15 has a date between 1065-1095 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IF E15:AM15, AO15:AP15, and AR15:AT15 has all dates within 335 days prior to today and IF AL15:AM15 and AR15:AS15 has all dates within 1065, "Safety Qualified"

Columns AN, AQ, AS will not be part of the formula.

ExceLoki · Sep 12, 2023

try this, i also included a version at the end for 365 users
--------------------

Nested If with dates & multiple day variables.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

P

Q

R

S

T

U

V

AA

AB

AC

AD

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AX

1

converting machine 1

converting machine 2

converting machine 3

converting machine 4

converting machine 5

converting machine 6

converting machine 7

converting machine 8

converting machine 9

corrugator 1

corrugator 2

corrugator 3

corrugator 4

corrugator 5

corrugator 6

corrugator 7

shipping 1

shipping 2

shipping 3

shipping 4

Powered 1

Powered 2

Powered 3

Powered 4

Powered 5

Powered 6

Powered 7

Powered 8

Powered 9

combined 365

14

ee last name

ee first name

status

supervisor

expired

15

expired

16

smith

incomplete

expired

17

smith

expired

9/10/22

9/11/22

9/12/22

9/13/22

9/14/22

9/15/22

9/16/22

9/17/22

9/18/22

9/21/22

9/22/22

9/23/22

9/24/22

9/25/22

9/26/22

9/27/22

10/2/22

10/3/22

10/4/22

10/5/22

10/13/22

10/14/22

10/13/22

10/14/22

10/13/22

expired

18

smith

saftey qualified

2/1/23

2/2/23

2/3/23

2/4/23

2/5/23

2/6/23

2/7/23

2/8/23

2/9/23

2/12/23

2/13/23

2/14/23

2/15/23

2/16/23

2/17/23

2/18/23

2/23/23

2/24/23

2/25/23

2/26/23

3/6/23

3/7/23

3/9/23

3/10/23

3/12/23

saftey qualified

Sheet1

Cell Formulas
Range	Formula
AX14:AX18	AX14	=LET(aem,TODAY()-MIN(E14:AM14,AO14:AP14,AR14:AT14),alam,TODAY()-MIN(AL14:AM14),aras,TODAY()-MIN(AR14:AS14),IF(OR(aem>365,AND(alam>1095,aras>1095)),"expired",IF(OR(AND(365>aem,aem>335),AND(AND(1095>alam,alam>1065),AND(1095>aras,aras>1065))),"expires soon",IF(AND(aem<335,AND(alam<1065,aras)),"saftey qualified",""))))
C15:C18	C15	=IF(A15="","",IF(CONCAT(E15:AT15)="","incomplete",IF(OR(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>365,AND(TODAY()-MIN(AL15:AM15)>1095,TODAY()-MIN(AR15:AS15)>1095)),"expired",IF(OR(AND(365>TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15),TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>335),AND(AND(1095>TODAY()-MIN(AL15:AM15),TODAY()-MIN(AL15:AM15)>1065),AND(1095>TODAY()-MIN(AR15:AS15),TODAY()-MIN(AR15:AS15)>1065))),"expires soon",IF(AND(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)<335,AND(TODAY()-MIN(AL15:AM15)<1065,TODAY()-MIN(AR15:AS15))),"saftey qualified","")))))

Charger_R_T · Sep 12, 2023

I received #NAME? error.

ExceLoki · Sep 12, 2023

Charger_R_T said:
I received #NAME? error.

which one did you use?
should be

Excel Formula:

=IF(A15="","",IF(CONCAT(E15:AT15)="","incomplete",IF(OR(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>365,AND(TODAY()-MIN(AL15:AM15)>1095,TODAY()-MIN(AR15:AS15)>1095)),"expired",IF(OR(AND(365>TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15),TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>335),AND(AND(1095>TODAY()-MIN(AL15:AM15),TODAY()-MIN(AL15:AM15)>1065),AND(1095>TODAY()-MIN(AR15:AS15),TODAY()-MIN(AR15:AS15)>1065))),"expires soon",IF(AND(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)<335,AND(TODAY()-MIN(AL15:AM15)<1065,TODAY()-MIN(AR15:AS15))),"saftey qualified","")))))

Charger_R_T · Sep 12, 2023

ExceLoki said:

which one did you use?
should be

Excel Formula:

=IF(A15="","",IF(CONCAT(E15:AT15)="","incomplete",IF(OR(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>365,AND(TODAY()-MIN(AL15:AM15)>1095,TODAY()-MIN(AR15:AS15)>1095)),"expired",IF(OR(AND(365>TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15),TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>335),AND(AND(1095>TODAY()-MIN(AL15:AM15),TODAY()-MIN(AL15:AM15)>1065),AND(1095>TODAY()-MIN(AR15:AS15),TODAY()-MIN(AR15:AS15)>1065))),"expires soon",IF(AND(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)<335,AND(TODAY()-MIN(AL15:AM15)<1065,TODAY()-MIN(AR15:AS15))),"saftey qualified","")))))

that's the one i used.

Fluff · Sep 12, 2023

Concat does not exist in 2016, it appeared with 2019.

ExceLoki · Sep 12, 2023

thanks @Fluff
try this instead
---------------

Excel Formula:

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AR15:AT15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>365,AND(TODAY()-MIN(AL15:AM15)>1095,TODAY()-MIN(AR15:AS15)>1095)),"expired",IF(OR(AND(365>TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15),TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>335),AND(AND(1095>TODAY()-MIN(AL15:AM15),TODAY()-MIN(AL15:AM15)>1065),AND(1095>TODAY()-MIN(AR15:AS15),TODAY()-MIN(AR15:AS15)>1065))),"expires soon",IF(AND(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)<335,AND(TODAY()-MIN(AL15:AM15)<1065,TODAY()-MIN(AR15:AS15))),"saftey qualified","")))))

Charger_R_T · Sep 13, 2023

ExceLoki said:

thanks @Fluff
try this instead
---------------

Excel Formula:

=IF(A15="","",IF(MAX(E15:AM15, AO15:AP15, AR15:AT15)=0,"incomplete",IF(OR(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>365,AND(TODAY()-MIN(AL15:AM15)>1095,TODAY()-MIN(AR15:AS15)>1095)),"expired",IF(OR(AND(365>TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15),TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)>335),AND(AND(1095>TODAY()-MIN(AL15:AM15),TODAY()-MIN(AL15:AM15)>1065),AND(1095>TODAY()-MIN(AR15:AS15),TODAY()-MIN(AR15:AS15)>1065))),"expires soon",IF(AND(TODAY()-MIN(E15:AM15,AO15:AP15,AR15:AT15)<335,AND(TODAY()-MIN(AL15:AM15)<1065,TODAY()-MIN(AR15:AS15))),"saftey qualified","")))))

I think it is getting closer. It's returning "expired" in the status cell when it should be "safety qualified" as none of the dates are in the expired range.

ExceLoki · Sep 13, 2023

can you provide some sample data with dates and expected outcomes for me to compare before submitting new formula?
suggest using xl2bb from link below

Nested If with dates & multiple day variables

Charger_R_T

New Member

Excel Facts

Charger_R_T

New Member

ExceLoki

Well-known Member

Charger_R_T

New Member

ExceLoki

Well-known Member

Charger_R_T

New Member

Fluff

MrExcel MVP, Moderator

ExceLoki

Well-known Member

Charger_R_T

New Member

ExceLoki

Well-known Member

Similar threads

Forum statistics

Share this page

Nested If with dates & multiple day variables

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP, Moderator

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock