Date manipulation

[Closed]
Report
Posts
2
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013
-
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
-
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

3 replies

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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.
Posts
2
Registration date
Tuesday October 8, 2013
Status
Member
Last seen
October 8, 2013

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
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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