Having trouble with a date aging formula

[massnative]

Hello all - I'm new to the forum so I'll do my best to ask the correct way.

I have three columns. Column One is a date reflecting a certain files expiration date. Column Two holds a formula =DATEDIF(A1,TODAY(),"d") that I use and it gives me what I've been looking for to reflect the maturity of the Column One. Column Three is a date the this is reviewed. Column Two has conditional formatting to show anything 3 to 7 days old to turn the cell red and anything over 7 days old to have a white cell with grey font. Any expiration dates that are in the future reflect a #NUM! warning because it's a future date.

What I am looking to do is two things; one is to show future cells in Column Two with nothing in it (or at least a zero), but still have the formula active. Second, I want to have a red cell in Column Two turn white with grey font as soon as any characters are put into the next cell in Column Three.

Overall, I am looking to have notification of any file that is between 3 and 7 days old (or older) reflect in red if no review is made.

Can anyone assist me with this? Much thanks!

If my picture link doesn't work, it looks similar to this:

A B C
1 9/27/10 2 <blank>
2 9/01/10 27 <blank> (B2 is now showing red)
3 9/5/10 24 9/29/10 (A3 is now grey having an entry in C3
4 10/15/10 #NUM! <blank> (I want B4 to be blank or have a zero)

I am using Windows XP with Excel 2007 (at work - a Mac at home)

 

[AlphaFrog]

Put this in B1
=IF(A1>=TODAY(),"",TODAY()-A1)

Format B1 as "General" or "Number". Excel may try to automatically set the format as a date.

Set these two Conditional Formats for B1. Condition1 has priority over Condition2.

Condition 1:
Formula is =NOT(ISBLANK(C1))
Format: Grey text, white background

Condition 2:
Cell value is greater than or equal to 3
Format: Black text, Red background

 

[massnative]

AlphaFrog - that's wonderful!

I had to add, however, one additional closed parenthesis at the very end of the initial formula, but that worked great!

One follow up for that - if I apply the formula to the entire A Column and there is no file entry date for future entries, the formula returns the full serial number. Any way to have that empty as well?

Also, the conditional formatting you gave doesn't seem to work, however, I am attempting this on MS Excel on a Mac. Would this possibly work on my PC at work?

Thanks again for your attention! A two week old mystery pretty much solved!

 

[AlphaFrog]

Try this in B1...

=IF(OR(A1>=TODAY(), ISBLANK(A1)),"",TODAY()-A1)


The Conditional Format (CF) should work on a Mac as well as a PC. Not sure what the problem is. I edited the CF formulas a few minutes after my initial post. Did you use the edited CF formulas?

 

[massnative]

Brilliant!

All went well except for one thing; for my future dates, Column B is blank - obviously, however, Column C has no date to enter which now leaves Column B highlighted in red. Any way around that? Otherwise, everything worked just as you suggested!

 

[AlphaFrog]

Condition 2:
Formula is =AND(B1<>"",B1>=3)
Format: Black text, Red background

 

[massnative]

I tried that as well just now and it doesn't seem to do anything different than the original Conditional 2 Format. I still have red cells with no data in them if Column C is empty, but there is nothing for future cells (Columns A,B,C) if they are blank - yay!

 

[massnative]

DISREGARD!!! My error, I inputted it for the wrong column and start line!

Awesome job AlphaFrog! My thanks for your time and help on this. You are helping me accomplish a great, but small, thing.

Have a great rest of your week!

All the best...

 

[AlphaFrog]

Glad it works.

Creating global harmony one excel formula at a time,
AlphaFrog