Excel wizards: setting row's fill color based on dates?

[NikPreviousAcct]

I've got a spreadsheet being used to track RMA's. There are three colors being used in the legend. A light green for "open," a gray for "closed," and a red for "overdue."

One of the columns is labelled "Date Opened" and another is labelled "Date Closed." I need to have each row colored based on the status of the RMA.

Is it possible to have the row colored the light green if there's no date listed in the Date Closed column, colored red if there's no date listed in the Date Closed column if the current date is more than 30 days after the date listed in Date Opened, and colored gray if there IS a date listed in the Date Closed column? Like, have the spreadsheet automatically color the entire row the appropriate color based on said criteria?

Is that even possible?

 

[NeoV]

you have a few options..

conditional formatting will work, but you'll have to copy the formatting to every cell in the row

VB would also work, but then you would have to run a macro after your data entry to do the color changes..

I'd start with conditional formatting, found in Data-Conditional formatting, and go from there.

 

[NikPreviousAcct]

I've got Excel 2002. I don't see "Conditional Formatting" under the Data menu. VB isn't an option. Ideas?

 

[KLin]

Originally posted by: Nik
I've got Excel 2002. I don't see "Conditional Formatting" under the Data menu. VB isn't an option. Ideas?

It's under the Format menu.

 

[ggnl]

1. Select the range of cells you want to be shaded starting with the first row (its important to start with the first row).

2. Go to Conditional Formatting in the Format menu.

3. There will be a little drop down menu that should say "Cell Value Is". Change it to "Formula Is".

4. Set up your formats.

4a. If your Date Opened is column B Date Closed is column C and, the first formula would be =$C2=0 and the format would be red shading. Note that the column reference is absolute and the row reference is relative. That would be your condition for no date.

4b. You would then have to add another condition (using the Add button) with the formula =TODAY()-$B2<30 and have green shading as the format.

4c. Your third condition would be =$C2>0 for the grey shading.

Let me know if this works

 

[NikPreviousAcct]

Originally posted by: ggnl
1. Select the range of cells you want to be shaded starting with the first row (its important to start with the first row).

2. Go to Conditional Formatting in the Format menu.

3. There will be a little drop down menu that should say "Cell Value Is". Change it to "Formula Is".

4. Set up your formats.

4a. If your Date Opened is column B Date Closed is column C and, the first formula would be =$C2=0 and the format would be red shading. Note that the column reference is absolute and the row reference is relative. That would be your condition for no date.

4b. You would then have to add another condition (using the Add button) with the formula =TODAY()-$B2<30 and have green shading as the format.

4c. Your third condition would be =$C2>0 for the grey shading.

Let me know if this works

That's awesome, thanks. The only thing that doesn't work is the overdue formatting. The date is with periods, in the format MM.DD.YYYY which isn't an option in date formatting. I have an internal RMA # being built that requires the date being formatted this way.

This is the code I have for deriving the internal RMA number. Column D is something I set by hand.

 

[NikPreviousAcct]

If I change the "." to "/" or the "," to "/" I get an error. I think Excel is trying to use the / function at that point instead of recognizing the "/" as an actual character in the date field. If someone knows how to fix this, then I can format the date columns with /'s instead of .'s and solve this issue.

...ideas?

 

[ggnl]

I think I'm a bit confused. You're using a format for the date that excel doesn't recognize as a date?

Remember that excel stores dates in serial format. So you wouldn't be able do date/time operations on the cells and still be able to do the operations you listed above.

An easy solution would be to just add another column with the date in a format that excel recognizes and work off that. You could hide the column so the spreadsheet is attractive for the users.

A harder solution would be to use the MID function and the DATE function to extract the characters from your date and put them in a format that excel recognizes.

The formula would be something like (assuming the date is in cell C3):

=DATE(MID($C3,7,4),MID($C3,1,2),MID($C3,4,2))

You could plug this formula into a larger formula to get the effect you want.

I'm not sure how you could make it work with the conditional formatting. Anyways, I hope this helps.

 

[NikPreviousAcct]

Anyone? Anyone know how to get Excel to recognize "/" as a delimiter instead of a divide function?

 

[NikPreviousAcct]

bump

 

[NikPreviousAcct]

Anybody?