Can I use conditional formatting to change the entry (e.g. in column F,
starting with F6) from "20050721" to a date format like 07/21/2005 with
the formula below?

=DATE(LEFT(F6,4),MID(F6,5,2),RIGHT(F6,2))

Please advice.

*** Sent via Developersdex http://www.developersdex.com ***

No. Conditional formatting just changes the way that a cell's value is shown.
It can't actually change the value.
You'd have to write some code to change the cell's value since the YYYMMDD
entry in a cell isn't recognised as a date (at least not in Excel XP)


"M H" wrote:

Can I use conditional formatting to change the entry (e.g. in column F,
starting with F6) from "20050721" to a date format like 07/21/2005 with
the formula below?

=DATE(LEFT(F6,4),MID(F6,5,2),RIGHT(F6,2))

Please advice.

*** Sent via Developersdex http://www.developersdex.com ***

Here is a code solution

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mDate

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
mDate = DateSerial(Left(.Value, 4), Mid(.Value, 5, 2),
Right(.Value, 2))
If IsDate(mDate) Then
.Value = mDate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

"M H" wrote in message
...
Can I use conditional formatting to change the entry (e.g. in column F,
starting with F6) from "20050721" to a date format like 07/21/2005 with
the formula below?

=DATE(LEFT(F6,4),MID(F6,5,2),RIGHT(F6,2))

Please advice.

*** Sent via Developersdex http://www.developersdex.com ***

Thanks, Bob. It's a magic!



*** Sent via Developersdex http://www.developersdex.com ***

No, it's event code <vbg

Bob

"M H" wrote in message
...
Thanks, Bob. It's a magic!



*** Sent via Developersdex http://www.developersdex.com ***