conditional formatting dates

  • Thread starter Thread starter yuen
  • Start date Start date
Y

yuen

i want to match the month in a series of dates, structured like this -
05/12/2008

any ideas? thanks!
 
05/12/2008

Is that May 12 2008 or December 5 2008?

If you want to conditionally format the cells based on the month:

Assume you have a range of dates in A1:A10

In Excel versions 2003 and earlier

Select the range of cells A1:A10
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=MONTH(A1)=n
Where n = the month number of interest: January =1 through December =12
Click the Format button
Select the desired style(s)
OK out

In Excel 2007

Select the range of cells A1:A10
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=MONTH(A1)=n
Where n = the month number of interest: January =1 through December =12
Click the Format button
Select the desired style(s)
OK out
 
im setting the date to uk format so 5th December 2008 and want to compare it
to today's date. how do i do that?
 
Ok, if it's for conditional formatting just change the formula to:

=MONTH(A1)=MONTH(NOW())
 
im hi-lighting cells i want to compare and clicking on condtional formatting
tab then new rule and entering the formula you have given with a1 as the date
of today e.g. b2 which has =today() but nothing is happening?
 
What version of Excel are you using?

Tell me *exactly* what cells you want to apply the conditional formatting
to.
 
Im using Microsoft Office 2007

D15:D24 are the dates i would like to compare. B2 is the cell with today's
date with formula =today() i just want to highlight the date with the same
month as current date.

Thanks!
 
Ok, follow these steps *exactly* ...

Select the cells in the range - Select cell D15 first then drag down to D24.
Cell D15 needs to be the active cell. The active cell is the one that is not
shaded. All the other cells in the selected range will be shaded.

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=MONTH(D15)=MONTH(B$2)
Click the Format button
Select the desired style(s)
OK out

If this doesn't work then your dates in D15:D24 are not true Excel dates.
You can test that by trying this formula:

=COUNT(D15:D24)

The result should be the count of dates in the range.
 
Back
Top