Excel 2010 - Conditional Formatting Dates 3

[pendle666]

Hello

I have a worksheet which contains dates in column A

[pre]
A
1 08/08/15
2 09/08/15
3 10/08/15
4 11/08/15

[/pre]


and so on. I want to use conditional formatting so the date that is a Sunday is reformatted to (in this case) Sun 09

I have so far:

=WEEKDAY(A$4:$A10,16)>1 with the format set as custom ddd, dd which comes out at Sun 01.

However, when applying the condition, my worksheet still looks like dd/mm/yy

16 I understand shows the day of the week to start on a Saturday, which is correct, so I'm assuming that >1 is meant to change days which are greater than the first day of the week.


thank you for helping.

thank you for helping

____________
Pendle

 

[dhookom]

Try change the formula to:

=WEEKDAY($A4,16)>1

Set the applies to:

A4:A10

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Conditional Fomatting cannot change a NumberFormat. It merely changes the cell shading, font colors, cell borders type of things.

You'll need VBA to change the NumberFOrmat...

Function FormatDate(r As Range)
    Select Case r.Value Mod 7
        Case 1: r.NumberFormat = "ddd d"
        Case Else: r.NumberFormat = "dd/mm/yy"
    End Select
End Function

Sub FormatDates()
[b]'SELECT the cells you want to change the NumberFormat and RUN[/b]
    Dim rng As Range
    
    For Each r In Selection
        FormatDate rng
    Next
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Skip, you are wrong, Conditional Format CAN Change the number format.

The formula for the Condition Format should be =WEEKDAY(A1)=1
The format needs to be Custom, with the Type being : "Sun" dd
The range for the Conditional Format Should be A1:A4

 

[SkipVought]

How did I miss that? Have a STAR!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Hello

Thanks for the replies.

My range should be A4:A10 as dhookom said, and not as in my original post.

However, I've done as advised, but my data now looks like this:

[pre]
05/09/15
Sun 06
Mon 07
Tues 08
Wed 09
Thurs 10
Fri 11
[/pre]

It's only Sun 06 which should look like that, the others need to be in dd/mm/yy

The conditional format is as follows:

[pre]
=WEEKDAY($A4:$A10,16)>1
[/pre]

and applies to: [pre]=$A$4:$A$10[/pre]




thank you for helping

____________
Pendle

 

[SkipVought]

EQUALS 1

NOT > 1!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Oops!

Thank you - have another star and congrats for winning best helper again

thank you for helping

____________
Pendle