Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Labels- POssible to show data value and data label together? | Charts and Charting in Excel | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Save 20% On Data Conversion and Data Formats Services by Data EntryIndia | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) |