Anonomise data
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 |
Anonomise data
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 |
Anonomise data
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 |
Anonomise data
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 |
Anonomise data: THANKS
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 |
All times are GMT +1. The time now is 12:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com