conditional formating for dates

[sathiyamoorthy]

hi all,

ups_date
11/1/2013 6:00
11/1/2013 8:00
11/1/2013 10:00
11/1/2013 12:00
11/1/2013 14:00
11/1/2013 16:00
11/1/2013 18:00
11/1/2013 20:00
11/1/2013 22:00
11/2/2013 0:00
11/2/2013 2:00
11/2/2013 4:00
11/2/2013 6:00
11/2/2013 8:00
11/2/2013 10:00
11/2/2013 12:00
11/2/2013 14:00
11/2/2013 16:00
11/2/2013 18:00
11/2/2013 20:00
11/2/2013 22:00
11/3/2013 0:00
11/3/2013 2:00
11/3/2013 4:00
11/3/2013 6:00
11/3/2013 8:00
11/3/2013 10:00
11/3/2013 12:00
11/3/2013 14:00
11/3/2013 16:00
11/3/2013 18:00
11/3/2013 20:00
11/3/2013 22:00
11/4/2013 0:00
11/4/2013 2:00
11/4/2013 4:00
11/4/2013 6:00
11/4/2013 8:00
11/4/2013 10:00
11/4/2013 12:00
11/4/2013 14:00
11/4/2013 16:00
11/4/2013 18:00
11/4/2013 20:00
11/4/2013 22:00
11/5/2013 0:00
11/5/2013 2:00
11/5/2013 4:00
11/5/2013 6:00
11/5/2013 8:00
11/5/2013 10:00
11/5/2013 12:00
11/5/2013 14:00
11/5/2013 16:00
11/5/2013 18:00
11/5/2013 20:00
11/5/2013 22:00
11/6/2013 0:00
11/6/2013 2:00
11/6/2013 4:00
11/6/2013 6:00
11/6/2013 8:00
11/6/2013 10:00
11/6/2013 12:00
11/6/2013 14:00
11/6/2013 16:00
11/6/2013 18:00
11/6/2013 20:00
11/6/2013 22:00
11/7/2013 0:00
11/7/2013 2:00
11/7/2013 4:00
11/7/2013 6:00
11/7/2013 8:00
11/7/2013 10:00
11/7/2013 12:00
11/7/2013 14:00
11/7/2013 16:00
11/7/2013 18:00
11/7/2013 20:00
11/7/2013 22:00

i need to highlight every date in different color... time is not a matter,, i want show the individual dates in one color group..

[sktneer]

As in your provided dates, days are same, so you can create 12 new rules for the months and set different color for each month.

After selecting the whole range of dates (Assuming your dates are in col. A and starts from cell A2), create a new rule with the following formula.

=MONTH($A2)=1

While using above formula in conditional formatting, you can fill a color of your choice and do it for every month by changing the red colored number 1 to 2,3,4...12 and change the fill color for every month. This is the only way as far as I know.

[ragulduy]

one way of getting just the date without time is:
=rounddown(date,0)

Dateserials are stored as XXXXX.XX where the numbers after the decimal place represent the time.

[Olly]

One way of doing it - if you want each day of the month to be a different colour:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
    If IsDate(c.Value) Then c.Interior.ColorIndex = Day(c) + 2
Next c
End Sub

[FDibbins]

This wont highlight each date a new color, bit it will highlight each time the date changes...

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =INT(A2)>INT(A1) format fill red (or whatever color you want

[sathiyamoorthy]

thanks to all,

especially for Fdibbins