I have a spreadsheet in XL2000 containing some data extracted from a DB. I
need to make the data anonymous so I can use it for training purposes. In
the date of birth column I want to change the dates so that
anyday/anymonth/1975 becomes 01/04/1975, anyday/anymonth/1976 becomes
01/05/1975, and so on.

My initial idea was a case statement applied to every cell in the column -
but I can't make it work.

Any assistance would be gratefully received.

WW

Not sure I quite follow your logic but using the functions DAY(A1)
MONTH(A1) YEAR(A1) you can split a date into it's component parts.
Then use DATE(year,, month, day) to get the new date.

therefore if cell A1 contains 31/Jan/2006 then
=date(year(A1)+11,Month(A1),20) returns 20Jan/2011

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=511020

Try the following function:

Public Function CleanDate(d As Date) As Date
CleanDate = DateSerial(1975, 4 + Year(d) - 1975, 1)
End Function

--
HTHs Martin


"Wicked Wizard" wrote:

I have a spreadsheet in XL2000 containing some data extracted from a DB. I
need to make the data anonymous so I can use it for training purposes. In
the date of birth column I want to change the dates so that
anyday/anymonth/1975 becomes 01/04/1975, anyday/anymonth/1976 becomes
01/05/1975, and so on.

My initial idea was a case statement applied to every cell in the column -
but I can't make it work.

Any assistance would be gratefully received.

WW

Hi Wizard,

Try:

'=============
Public Sub Tester001()
Dim Rng As Range
Dim rCell As Range
Dim LRow As Long

LRow = Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A2:A" & LRow)

For Each rCell In Rng.Cells
With rCell
If Not IsEmpty(.Value) Then
If IsDate(.Value) Then
.Value = DateSerial(Year(.Value), 4, 1)
End If
End If
End With
Next rCell

End Sub
'<<=============


---
Regards,
Norman



"Wicked Wizard" wrote in message
...
I have a spreadsheet in XL2000 containing some data extracted from a DB. I
need to make the data anonymous so I can use it for training purposes. In
the date of birth column I want to change the dates so that
anyday/anymonth/1975 becomes 01/04/1975, anyday/anymonth/1976 becomes
01/05/1975, and so on.

My initial idea was a case statement applied to every cell in the column -
but I can't make it work.

Any assistance would be gratefully received.

WW

Amazing: one only has to ask and the Cavalry comes riding over the hill.
And at the first blast of the bugle too.

(Oh dear, I am watching too many Westerns.)

Many thanks to you.

WW


"Wicked Wizard" wrote in message
...
I have a spreadsheet in XL2000 containing some data extracted from a DB. I
need to make the data anonymous so I can use it for training purposes. In
the date of birth column I want to change the dates so that
anyday/anymonth/1975 becomes 01/04/1975, anyday/anymonth/1976 becomes
01/05/1975, and so on.

My initial idea was a case statement applied to every cell in the column -
but I can't make it work.

Any assistance would be gratefully received.

WW