Problem: Function not detecting the output from a series of functions

[FRESHShaZaM]

GENERAL​

Problem: Function not detecting the output from a series of functions.

Question: Is there a way to fix?

_____________________________________________________________________________

SPECIFIED​

Data Sheet:

I’ve only included the necessary columns. Don’t worry about what is in I, J, or F because they don’t affect what’s going on. The three highlighted columns have formulas.

A1​	B​	C​	E​	G​	H​	J​	K​	L​	M​
2​	LAST​	FIRST​	STATUS​	HIRED​	ELIGIBLE​	RECEIVED​	EFFECTIVE​	WITHDRAWN​	LOA​	COMMENT​
3​	Holder​	Bill​	Active​	7/2/2018​	9/30/2018​		9/30/2018​
4​	Smith​	John​	Withdrawn​					12/9/2019​
5​	Howard​	Jamie​	Processing​	8/12/2019​	11/10/2019​	12/25/2019​	12/25/2019​
6​	Lee​	Paris​	Processing​
7​	Steward​	Tucker​	LOA​						9/22/2018​

Column reliance:

H -> G

K -> J and H

E -> L, M, and K

Formulas:

H3=IF(G3>0.01,G3+90," ")

K3==IF(J3>=H3, J3, H3)

E3=IF(L3>0.01,"Withdrawn",IF(M3>0.01,"LOA",IF(K3>TODAY(),"Processing","Active")))

Explanation:

The equation in column E is not properly reading column K. It will only read K if J is filled or G and H are filled.

If H outputs a date instead of “ “ it will still not work.

If H outputs “1” or date instead of a “ “ it will still not work.

 

[Joe4]

I do not understand your question.

Which of your 5 example lines are not working?
Of those lines which are not returning the correct values, what values should they be returning and why?

 

[FRESHShaZaM]

I do not understand your question.

Which of your 5 example lines are not working?
Of those lines which are not returning the correct values, what values should they be returning and why?

Maybe I should add more detail, but this line:

The equation in column E is not properly reading column K. It will only read K if J is filled or G and H are filled.

Plus:

The point is, that J, G, and H are not always filled. E3=IF(L3>0.01,"Withdrawn",IF(M3>0.01,"LOA",IF(K3>TODAY(),"Processing","Active"))) should be able to read that they aren't filled and not greater than today, so therefore active. But it doesn't.

 

[Joe4]

I don't think you answered my question.

You posted 5 example lines.
Please explain what each one should be returning in column E status and explain why (the logic behind it).

Better yet, forget all your formulas. Just explain in plain English all the possible scenarios and what you want to happen in each case.

 

[FRESHShaZaM]

I don't think you answered my question.

You posted 5 example lines.
Please explain what each one should be returning in column E status and explain why (the logic behind it).

Better yet, forget all your formulas. Just explain in plain English all the possible scenarios and what you want to happen in each case.

How's this...

A person's status (column E) is 1 of 4 possibilities: Withdrawn, LOA, Processing, and Active.

Withdrawn should display if the person has a date under the Withdrawn Column (Column L).

LOA should display if there isn't a withdrawn date, and there is a date listed under the LOA column.

Processing should display if there isn't a withdrawn or LOA date, and there is a listed Effective date that is after today, by today meaning the day you open the spreadsheet.

Active should display if there isn't a withdrawn or LOA date, and the Effective date is today, before today, or blank.

 

[Joe4]

OK, I think I see the issue. It is when the formula in column H is returning a space.
Try this for the formula in column E, which should handle that:
=IF(L3>0,"Withdrawn",IF(M3>0,"LOA",IF(AND(K3>TODAY(),K3<>" "),"Processing","Active")))

 

[FRESHShaZaM]

OK, I think I see the issue. It is when the formula in column H is returning a space.
Try this for the formula in column E, which should handle that:
=IF(L3>0,"Withdrawn",IF(M3>0,"LOA",IF(AND(K3>TODAY(),K3<>" "),"Processing","Active")))

When I finished typing out my "plain English" I started thinking of something similar, but you got it before me. Thanks so much! This works!!

 

[Joe4]

You are welcome.

When I finished typing out my "plain English" I started thinking of something similar,

Yep, sometimes it helps to take a step back. It is easy to sometimes "lose the forest for the trees".
I cannot tell you how many times I have gotten stuck on a problem, and have figured it out when I have started to type out my question (and then it hits my over the head!).