Conditional formatting of dates

BIGPHILL

New Member
Joined
Dec 14, 2017
Messages
11
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,168
Office Version
  1. 2010
Disregard this post - I made a mistake
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,408
Office Version
  1. 365
Platform
  1. MacOS
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))
 
Last edited:

BIGPHILL

New Member
Joined
Dec 14, 2017
Messages
11
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

New Member
Joined
Dec 14, 2017
Messages
11

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Re: Urgent Help Required

For green use
Code:
=AND(TODAY()-G5<=14,TODAY()>G5)

for yellow
Code:
=AND(TODAY()-G5<=31,TODAY()>G5)

for red
Code:
=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

Well-known Member
Joined
Oct 24, 2012
Messages
4,408
Office Version
  1. 365
Platform
  1. MacOS
same principle

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

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


red


</today()-31
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,944
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 14, 2017
Messages
11
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top