Conditional formatting of dates

[BIGPHILL]

I have a date column in my spreadsheet. I want the date in the cell to go Red if the date has come and gone, amber if the current month and green if the date is ahead.

I know there is conditional formatting, however it only allows me to choose next month to go green, and last month to go red, not all the other months for example 2 months in advance.


Anyone have any suggestions for me on how to do this.

Thanks

 

[arthurbr]

Disregard this post - I made a mistake

 

[etaf]

use a formula
and you can setup three rules

Lets assume the dates are in column A

then the formula would be

for dates for the 1st of this month
=A1<date(year(today()),month(today()),day(1))
Format RED - which will be all dates before the 1st - did you want to limit that

Green is ahead, ahead of what the Date today or the end of the current month ?

so green if 1st jan 2018 or green if greater than today

as you say amber is current month

assuming only upto the date

Amber is from 1st of this month until current date

=AND(A1>=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(1)), A1<=TODAY())
Format Amber

then for green

=A1>today()

did you want to limit the green to just 2 months ahead</date(year(today()),month(today()),day(1))

 

[BIGPHILL]

Many thanks in your reply. I spoke to my boss now and he has decided that if a date in one cell is in within the last 14 days then for that cell to go green, and if its in the last 15-31 days, the cell to go amber, and if over 32+days the cell to go colour red.

I have used this formula =AND($G5-TODAY()>=-14,$G5-TODAY()<=0) on the conditional formatting enter formula tab and this goes green and works, however i cant get the 15-31 days (Amber) & the +32 days (Red) to work with the same formula.

Any suggestions, many thanks,

 

[BIGPHILL]

Urgent Help Required

I need help with conditional formatting. What a require is small, If a date in one cell is in within the last 14 days then for that cell to go green, and if its in the last 15-31 days, the cell to go amber, and if over 32+days the cell to go colour red.

I have used this formula =AND($G5-TODAY()>=-14,$G5-TODAY()<=0) on the conditional formatting enter formula tab and this goes green and works, however i cant get the 15-31 days (Amber) & the +32 days (Red) to work with the same formula.

Any suggestions, many thanks,

 

[mole999]

Re: Urgent Help Required

principle is right, you may need to change the order they are applied > 14 will always be true before > 32, so start red, then amber then green

 

[Scott T]

Re: Urgent Help Required

For green use

=AND(TODAY()-G5<=14,TODAY()>G5)

for yellow

=AND(TODAY()-G5<=31,TODAY()>G5)

for red

=AND(TODAY()-G5>=32,TODAY()>G5)

Go into manage rules and make sure the green rule is on top and click stop if true
next should be the yellow rule and stop if true should be checked
The red rule should be last

 

[etaf]

same principle

=AND($G5>=TODAY()-31,$G5<=TODAY()-15)
for amber

=g5<today()-31
< </today()-31
TODAY()-31<today()-31


red


</today()-31

 

[Joe4]

I have merged your two threads together.

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.

 

[BIGPHILL]

Brill thank you, this has worked. However as i apply the same rules to other cells in the same column as i go down, if no date is shown in the cells in the column, they automatically go red. Is there a way of eliminating this, so it only go red when a date is entered which is longer than 31 days.

Thanks all for your help