Previous Topic Next Topic

Date manipulation

Closed
Medicjohn Posts 2 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013 - Oct 8, 2013 at 02:51 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Oct 9, 2013 at 03:23 PM
Hi,

I have 4 columns approx 250 lines long. They contain expiry dates in the dd/mm/yyyy format.

I wish to compare these dates against another date in a single cell.

If the dates are equal to or less than the single date I need them to be shaded red.
if the dates are equal to or less than the date plus 2 months I need them to turn Yellow
If the dates are greater than the single date plus 2 months I need them to be highlighted green.
Any cells that do not have an entry in them I need to be clear.

I have tried for hours using the inbuilt tool (conditionall formatting) but cant get anything other than a greater than the date to do anything.

Any help would be appreciated. Am using 2007 and a PC.

John
Related:

3 responses

Answer 1 / 3
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 8, 2013 at 06:31 AM
John, Good morning.

Suppose your date in a single cell is at G1

If you need to compare A1 to this cell try this one:

1st Condition --> =$A1>=$G$1 --> RED

2nd Condition --> =($G$1-$A1)<60 --> YELLOW

3rd Condition --> =($G$1-$A1)>=60 --> GREEN

Is it what you desire?
Please, tell us if it worked for you.

I hope it helps.

Greetings from Brazil.
Answer 2 / 3
Medicjohn Posts 2 Registration date Tuesday October 8, 2013 Status Member Last seen October 8, 2013
Oct 8, 2013 at 08:28 PM
Hi Brazil (Mazzaropi),

Thank you so much. I based my formula on your method. For some reason the syntax was incorrect and I had to alter it before I got the correct result.

The arithmetical functions had to be in brackets or it would not work.

The only thing I cannot do now is have the cells without any data (date entry) remain clear. At present they are calculating as less than the set date and are therefore shaded appropriately.

Is there a way to achieve this?

Thanks again

John
Answer 3 / 3
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 9, 2013 at 03:23 PM
John, Good afternoon.

"...The only thing I cannot do now is have the cells without any data (date entry) remain clear..."

I did a simple example for you.
http://speedy.sh/3Eq4u/08-10-2013-em-KIOSKEA-Date-manipulations-MedicJohn-OK.xls

Take a look at it and tell us if it worked for you.

I hope it helps.
Best regards,

----------------------------------------------------------------------------------------------------
Belo Horizonte, Brasil.
Marcílio Lobão