I need some help. I am using excel to put together a maintenace log and I
cannot seem to get it to work the way I want it to. Here is my problem,
besides not knowing what I'm doing of course.
I have three columns for the maintenance dates. C2 is "Initial" (I got this
down, I put in the date), C3 is "Annual Due Date" (Again I think I got this
licked, I put in C2+365). Now here's where I'm pulling my hair out. I have
several things I want C3 to do and can't seem to get it to work. FIRST, I
want it to turn red when it is 30 days until the annual is due (C2) or
overdue. Second, when I put the date in C3 and it is within the year I want
it to turn green (Of course minus the time when it's within the thirty days
or overdue for annual maintenance it should be red), And if C3 is blank I
want it to be, well blank (this is for new equipment)
If anyone can help me I would truly appreciate it. Thanks!

Here are the formulas for C3. I know you want the color to be red 30 days
prior to the due date

Condition 1: Set to "Formual Is" and add the following:
=AND(B1<"", TODAY()=A1,TODAY()<A1+365-30)
This is for the green formatting

Condition 2: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()=(C2+365-30))
This is the 30 day period or overdue.

If you would allow me to make a suggestion: I would consider a different
color coding in order to be easier on the eyes, easier on the color printer,
and to better catalog priority.

1) As long as the due date is with the normal range and not within the 30
day period or overdue, leave the font color black and background white. No
conditional formatting is needed.

2) When the due date is in the 30 day range, make the font color black and
the background orange. This will be CONDITION 1 in the below format.

3) When the due date is over due, change the font color to white and
background red. See CONDITION 2 formula below.

CONDITION 1: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()=(C2+365-30),TODAY()<=C2+365)

CONDITION 2: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()C2+365)

Using the format will help you to pick out those that are overdue faster and
need to be prioritized.

Good Luck,
Les


"SSGMike" wrote:

I need some help. I am using excel to put together a maintenace log and I
cannot seem to get it to work the way I want it to. Here is my problem,
besides not knowing what I'm doing of course.
I have three columns for the maintenance dates. C2 is "Initial" (I got this
down, I put in the date), C3 is "Annual Due Date" (Again I think I got this
licked, I put in C2+365). Now here's where I'm pulling my hair out. I have
several things I want C3 to do and can't seem to get it to work. FIRST, I
want it to turn red when it is 30 days until the annual is due (C2) or
overdue. Second, when I put the date in C3 and it is within the year I want
it to turn green (Of course minus the time when it's within the thirty days
or overdue for annual maintenance it should be red), And if C3 is blank I
want it to be, well blank (this is for new equipment)
If anyone can help me I would truly appreciate it. Thanks!

THANK YOU!!!!!
And my Soldiers also thank you. I went from not saying very nice things to
the computer (Because you know it was the computers fault. LOL), to having a
smile on my face and am even going to let my Soldiers of early today. Also, I
used your color suggestion, It'll work well for me. Thanks Again.

"WLMPilot" wrote:

Here are the formulas for C3. I know you want the color to be red 30 days
prior to the due date

Condition 1: Set to "Formual Is" and add the following:
=AND(B1<"", TODAY()=A1,TODAY()<A1+365-30)
This is for the green formatting

Condition 2: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()=(C2+365-30))
This is the 30 day period or overdue.

If you would allow me to make a suggestion: I would consider a different
color coding in order to be easier on the eyes, easier on the color printer,
and to better catalog priority.

1) As long as the due date is with the normal range and not within the 30
day period or overdue, leave the font color black and background white. No
conditional formatting is needed.

2) When the due date is in the 30 day range, make the font color black and
the background orange. This will be CONDITION 1 in the below format.

3) When the due date is over due, change the font color to white and
background red. See CONDITION 2 formula below.

CONDITION 1: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()=(C2+365-30),TODAY()<=C2+365)

CONDITION 2: Set to "Formula Is" and add the following:
=AND(C3<"", TODAY()C2+365)

Using the format will help you to pick out those that are overdue faster and
need to be prioritized.

Good Luck,
Les


"SSGMike" wrote:

I need some help. I am using excel to put together a maintenace log and I
cannot seem to get it to work the way I want it to. Here is my problem,
besides not knowing what I'm doing of course.
I have three columns for the maintenance dates. C2 is "Initial" (I got this
down, I put in the date), C3 is "Annual Due Date" (Again I think I got this
licked, I put in C2+365). Now here's where I'm pulling my hair out. I have
several things I want C3 to do and can't seem to get it to work. FIRST, I
want it to turn red when it is 30 days until the annual is due (C2) or
overdue. Second, when I put the date in C3 and it is within the year I want
it to turn green (Of course minus the time when it's within the thirty days
or overdue for annual maintenance it should be red), And if C3 is blank I
want it to be, well blank (this is for new equipment)
If anyone can help me I would truly appreciate it. Thanks!