Show Blank in date formulas

[bamaisgreat]

Im not if fixing one of these formulas will fix the others but In cell J3 i have formula =I3+1825, I3 is a date. In K3 i have =J3-TODAY() and L3 I have =IF(K3<0,"EXPIRED",IF(K3<30,"EXPIRING SOON","IN DATE")). When I copy these down and I have blank rows I need them to just be empty.

 

[Joe4]

Pick some cell that should have a date, but is empty. Let's say it is cell I3.
Then, wrap your current formula in an IF statement, like this:
=IF(I3="","",your formula)

 

[bamaisgreat]

That worked great.
This the only other one that is showing up VALUE# =IF(K3<0,"EXPIRED",IF(K3<30,"EXPIRING SOON","IN DATE"))

 

[Joe4]

In K3 i have =J3-TODAY()

The only way this will return #VALUE is if there is a text entry in cell J3. If there is any number, or nothing, it will return a value.
Note that a single space is NOT the same as being empty! A single space is actually a non-empty text entry, so it would geneerate that error.
So, you need to confirm what actually is in column J for any "empty" row.

You can use the LEN function to check its contents, i.e.
=LEN(J3)
If it returns anything other than 0, it is not empty.

 

[bamaisgreat]

sorry about that. I had the formula pulled down before I fixed the one in front of it.

The formula I have in L3 is =IF(K3<0,"EXPIRED",IF(K3<30,"EXPIRING SOON","IN DATE"))

It is showing "IN DATE" when its in a blank row.

 

[Joe4]

You want to nest one more check, just like I showed you up in my initial reply:

[COLOR=#333333]=IF(K3="","",IF(K3<0,"EXPIRED",IF(K3<30,"EXPIRING SOON","IN DATE")))[/COLOR]

 

[bamaisgreat]

Thanks. I will study on this.

 

[Joe4]

You are welcome. Note I showed the structure back up in my first reply.
Essentially, you are taking your original formula, and inserting it into the "your formula" part.
What it then does is if first check some designated cell to see if it is blank, and if it is, return blank, otherwise, do your the calculation from your original formula.

 

[bamaisgreat]

Ive been working on what we discussed above but Ive run into something I cant figure out.
In the formula below when C3 gets to 365 it shows "WITHIN 1 YEAR" but when C3 gets to 30 I need it to show "WITHIN 30 DAYS"
I believe I have to IF statements working against each other.

=IF(C3="","",IF(C3<0,"EXPIRED",IF(C3<365,"WITHIN 1 YEAR",IF(C3<30,"WITHIN 30 DAYS","IN DATE"))))

 

[Joe4]

When nesting IF statements like that, you always either need to work from lowest to highest, or vice-versa. You need to do this because once it hits a TRUE conditions, it stops there.

So, you currently have the following order:
1. Check if empty
2. Check if less than zero
3. Check if less than 365
4. Check if less than 30

Following that progression, if you have a value greater than zero and less than 30, it will NEVER get past step 3 (because anything less than 30 also has to be less than 365).
So you need to switch the order of your last two clauses there.