Highlight dates passed - confitional formatting

Obviously with Now and weekday functions you can do a number of conditional formatting.

I have a list on my spread sheet. The list has all my direct debits and the date each one comes out, i.e. 1 meaning the first of the month or 27 for the 27th. I just want to put the date in rather than have a spread sheet for each individual month. So the list is just 1,2,3,4,5,6 etc.

What I am looking to achieve is for the spread sheet to reset at the beginning of the month and the dates cells are red and when the date comes and passes then to turn that cell green.

Any ideas how I can do this, if it is possible?

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>

The range you want to apply to - for example all of column A


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=A1<today()


Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

But do you just plan to have the numbers
1-31 ?
or actual dates ?

if so then again assuming in column A1

=A1< day(today())

It is always a best practice to use dates in Excel when you mean for them to represent dates.

If you insist on putting in just the day number, then etaf's formula must be adjusted as follows. It will assume that the day is in the current month:

Formula:

Please Login or Register  to view this content.

what formula
=A1< day(today())

so this will turn any day before and including todays date to green
so as its the 19th - every thing upto 19 is green

tomorrow 20 will be green

why 27 - we are only at the 19th

see attached sample

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

you need to apply to the column you are using and the range

I have also modified to exclude blanks

see attached

your welcome - so you need to apply to the range you want the conditional formatting to apply to
and then use the first cell reference in that range

thanks for the rep