jumping rowsand highlighting cells

K

Karine

Hello,

I have equipment that need to have a maintainence checks every 3/6/12
months. I have created a workbook for this data ( equip, week and days).
However rather then going and highlighting the cells manually is there some
kind fo formating i can do so it reads that after 90 days to highlight the
cells the colour it should be (There is about 900 equipment so it would be a
great help to just format each one rather then go threw the entire year for
all of them)
Hope thats clear enough.Thanks in advance.

Karine
 
B

Bernie Deitrick

Karine,

Your problem statement is not clear enough.

Do you keep a list of dates that the equipment was maintained on? How do you indicate if the
maintainence is required in3 months or 6 months or 12 months?

Anyway, Conditional Formatting will do what you want - but the specifics depends on your actual data
entry. Post a couple of rows in a message, and explain what you would want to have happen based on
that data sample.

HTH,
Bernie
MS Excel MVP
 
K

Karine

The months that the checks are required I will collect from other sources.
WEEK 1 week
2 M T W T F S S
Equipment Name 31 1 2 3 4 5 6 etc...
ROBOT 1

Thats what the workbook likes like but it goes for 52 weeks with the same
set up. If The maint check for Robot 1 was on (Monday 31 and is checked every
three months) i would want to highlight i would want to skip 90 (cells/days)
over and highlight that day.
Hopefully that clears things up a little bit.
Thanks again.
Karine
 
B

Bernie Deitrick

Karine,

Sorry, that isn't clear. Do you store a date anywhere? How do you indicate that maintenance
occurred? What are your headers? Do you have one column for every day?

HTH,
Bernie
MS Excel MVP
 
K

Karine

If i had cell A3 highlighted and I wanted to highlight 90 days from that cell
whats my formula? I have tried NOW=()+90, & =AND (A3<>,""A3<TODAY()+90) in
conditional formatting

I just want to highlight the cells, somehow I wanted the formula to know I
want it to skip 90 days/cells ahead and highlight the same color in that cell.
Hopefully this is clear enough. I'm sorry I'm trying to be clear but it's a
hard chart to explain.

Karine
 
B

Bernie Deitrick

Karine,

Let's say that you have a date in cell A3 - today's date Nov 29, 2007. 90 days from now is =A3+90
or Feb 27, 2008.

Where would the date Feb 27, 2008 be located on your sheet? Is it in a header row? Is it down a
column? Is it filled into cell B3? Do you have a formula somewhere that would return that date? Or,
and this is what I am beginning to think, are you really just interested in using a formula to
return that date?

If you really just want the date, then use

=A3+90

Or, if A3 might not be filled in, you could use this to keep the cell looking blank until A3 is
filled in:

=IF(A3="","",A3+90)

BUT If you really want 3 months instead of 90 days, then use

=DATE(YEAR(A3),MONTH(A3)+3,DAY(A3))

For 6 months:
=DATE(YEAR(A3),MONTH(A3)+6,DAY(A3))

For one year:
=DATE(YEAR(A3)+1,MONTH(A3),DAY(A3))

HTH,
Bernie
MS Excel MVP
 
K

Karine

Thank-you Bernie for your patience and your respond,
That's what i was looking for. That's just Great! It should work.
Karine
 
Top