Icon sets used for date expiry's

[rhyse1977]

Hi,

I'm hoping someone can help.

I have dates that i want to use the icons against to show the green tick when they have more than 60 days untill the date in question, the yellow ! for when they have 30 days, and the red x for when they are overdue.

I tried using the formula =today()-60 and =today()-30, to get the result, but everything just shows as a tick.

look forward to hearing your solutions, and thanks in advance.

 

[rhyse1977]

can anyone help, even a 'you cant do it' would be good

thanks

 

[CWatts]

Most people at this time of the day are working and don't always have the leeway to check these forums, so there's no need to keep bumping the post...

The formulas you're using only go in the past, you need to reverse it if you want to find out "what if" for the future. If you have the due date in B2, you can use the following formula:

=B2-TODAY()

So if you then go to Conditional Formats > Icon Sets > More Rules and set green for >=60, set yellow for >= 0 days, and it will default red to <0 days, which would be over due.

 

[rhyse1977]

Sorry i was being a little impatiant as i wanted to get it finished for a meeting i had that day.
I didnt manage to sort it out in the end and just used the old way of doing it, with the colouring of cells.

However now i have a bit more time i'd like to try and get this working. I have had a look at what you listed as the resolve above, but i cant get it working. ive attached the spreadsheet below, so hoping someone can solve it for me.

Just to reiterate, i need the end date to show the tick when more there is more than a month left, the ! when there is a month and the x when its gone past the end date.

thanks in advance for any help i might get

<TABLE style="WIDTH: 495pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=660><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 229pt; mso-width-source: userset; mso-width-alt: 11154" width=305><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 101pt; FONT-FAMILY: Calibri; BACKGROUND: #0070c0; HEIGHT: 30.75pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" id=td_post_2684021 class=xl64 height=41 width=135>Suppliers Name</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 229pt; FONT-FAMILY: Calibri; BACKGROUND: #0070c0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 width=305>Description</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 82pt; FONT-FAMILY: Calibri; BACKGROUND: #0070c0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl63 width=109>Start Date</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 83pt; FONT-FAMILY: Calibri; BACKGROUND: #0070c0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl63 width=111>End Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65 height=20>Power testing limited</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl65>Service of HV switchgear</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl66>12/03/2011</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl66>11/03/2012
 

</TD></TR></TBODY></TABLE>