Injecting Random Data in DATE format

**dbd08** · 12-16-2009, 01:25 PM

I have a large list of records that I need to randomly inject data into. Some of these records already have a valid date entered, so I don't want these changed. I just need the field that have the word "NULL" (no, they are not actual NULL fields), to have a random date entered in MM/DD/YYYY format.

I'm assuming this will include something like this

=RANDBETWEEN(1,12),"/",RANDBETWEEN(1,30),"/",RANDBETWEEN(1920,1990)

But I'm not really sure. It'd also be awesome if, when the month equals 2 (February), the day cannot equal more than 28. But that's not really necessary.

I've spent some time on this but just can't figure it out, so hopefully someone brighter than I can help.

**DonkeyOte** · 12-16-2009, 01:36 PM

In XL dates are integers so just use

=RANDBETWEEN("1/1/1920","12/31/1990")

Regards population - assuming your existing dates are real date values (ie numbers) - highlight your entire range - F5 -> Special -> Constants -> Text Values -> OK

now type in the above formula and press CTRL + ENTER

EDIT: on an aside - judging by your first post I assume you know that pre XL2007 use of RANDBETWEEN requires activation of Analysis ToolPak Add-In (via Tools - Addins)

**dbd08** · 12-16-2009, 01:44 PM

I tried this and all the field were populated with numbers 22372 and 27603

So I know this is not working because of the "03", and the fact that it should be going to only 1990, even if it's only in YY format.

Any ideas? I am using Excel 2007

**DonkeyOte** · 12-16-2009, 01:48 PM

Originally Posted by dbd08

I know this is not working because of the "03", and the fact that it should be going to only 1990, even if it's only in YY format.

If you re-read my post you'll note that I said in XL dates are integers

27603 being 28th July 1975
(1900 date system).

you need simply format your cells to Date rather than General

on an aside please do not quote entire posts in reply this clutters the board.

**dbd08** · 12-16-2009, 01:48 PM

Nevermind! I just had to "Format Cells" as DATE and everything started looking correct.

Thanks!

Any reason why they appear like that before? For instance, 30331 turned into 8/6/1970