Mike and Bill offer different ways of generating a random date between two dates. Episode 1095 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! I'm Bill Jelen from MrExcel.com and I've got a cool excel tip for you, today.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that Alright! Welcome back time for another dueling Excel podcast.
I'm bill Jelen from MrExcel.
Mike Girvin from Excel Is Fun.
Mike came up with the idea, today.
How do we generate a random date between two dates.
Now, I have to tell you, I'm in the process of rewriting all my books for Excel 2010.
So, I am the king of generating random data.
Right now for example, region let's say we had five regions equal R ampersand, Rand between comma 1 comma 5.
I'll just randomly generate some data down their, products.
How about equal CHAR rand between 65 that's an A and 69 that's going to give us products A through E and just to mix up a little bit ampersand rand between 11 comma 29.
There we go.
Miss those parentheses again.
I should use Mike's trick and making sure that I had the last one is the black parentheses, okay!
But dates are a little tough because with dates it would be nice, just to be able to type rand between 2/1/2008 but unfortunately that's how Excel displays the date.
That's not really how it's storing the date.
If I would press [ ctrl ] and the backwards accent.
The grub accent, you'll see that those dates are 39479, but who really knows, who knows that.
I mean that would be ridiculous.
I will just type that in.
So, I end up always using the helper cells up there equal rand between this first date, press [ F4 ] comma and then the second date and press [ F4 ].
Now, I'm gonna have to format that as a date.
So, [ ctrl + 1 ], choose date, choose the format that I link and double click to copy it down, alright.
But for me this is okay because I take the data, I convert it to values and those forms don't persist.
I don't have to keep these helper cells up here.
Cool trick though, if we wanted to keep these forms and we wanted to get rid of the helper cells up here in the formula bar.
I'm going to select C1 and press the [ F9 ] key.
[ F9 ] will convert that to the real value and then D1 and press the [ F9 ] key, that will also convert it to the real value and we'll copy that down and now we have a formula that does not rely on these helper cells up there.
So, there you have it.
That's my method.
I'll see what Mike has in store for us.
Mike: Thanks, MrExcel. Hey!
Randomizing dates, you do have to be a little bit careful.
Just because a date as MrExcel set is actually a serial number.
Now, if I were going to do region and product there's lots of ways to do.
So, another way is to use the index is that same trick, MrExcel highlighted the range, right here and hit [ F9 ] and then rand between 1 and 6.
So, we could just have some some words in essence.
We're looking up, and you could do the same thing for product here, but I only have five.
Now, date...
We'll use the same rand between but what about this if you don't have the date and cell, a helper cell and you don't have memorized the serial number.
Just use the date function.
Date needs a year month and a day, so I'm going to put 2008 comma the month is 2 comma the day is 1.
So that's February 1st, 2008.
I think that's a leap year.
I think, there was 29 days in that month.
Come on, then the top is going to be date 2008 comma 3 31.
Close parentheses, close parentheses.
So, that's another way and if you highlight these right here and [ F9 ], you can see they actually give you the serial number.
So, that's one nice use of the date function.
You can actually get the serial number.
Another way if you have an earlier version 2007 and you don't have the analysis tool pack added in under, tools add-in and you don't have the rand between.
You'd have to do something like this, how that equals date.
So, that's the starting point.
That's the minimum and we need to add to it some randomized number between 1 and 59 because there's 59 days between this date and the final date.
So, we want to use the INT function.
The INT takes the integer part, always going down of some number and then we use Rand.
Rand will generate a number between 0 and 1 and we're going to do, not 59.
So, we'll take Rand which generates the number between 0 and 1 but imagine if we got point 5 here right, point five times actually 60 would give us 30 days.
So, it add 30 days but the thing about rand is taking really close to zero, which means this can add zero which is fine because sometimes we hadn't want to add zero.
We want this bottom date, but on the top end we want to only add 59.
So, we actually added the extra number here.
That way we go from zero to 59 because it'll never quite get to 60.
Close parentheses and [ ctrl enter ] and there you have two formulas for randomizing dates between February 1st, 2008 and March 31st, 2008 all right, we'll see you next trick.
MrExcel: Okay! Now, Mike you confuse me on that one because you wanted numbers between 0 and 59 and so you asked for the Rand times 60, one more.
I had to come here to convince myself.
So, I wanted random numbers from 1 to 10 and I used Rand times 11 using your theory and sure enough we get numbers from 1 to 10.
But what I guess that I missed was that you also wanted a zero.
So, here we have some zero showing up as well.
So, if you use rand times 60, we're going to get 60 different numbers ranging from 0 up to 59.
So, if I really want random numbers from 1 to 10, then what I need to do is Rand times 10 and then at the end plus 1 and we'll copy that down.
Just have a little frequency distribution over here as I press [ F9 ].
You'll see that we are now getting numbers from 1 to 10.
So, Mike you're absolutely right.
Throw me for a little loop, how to come prove it to myself.
Why did I ever question it?
Hey, for everyone out there.
Thanks for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
Hey, this is Mike Gel Girvin from Excel Is Fun on YouTube and I have a different way to do that Alright! Welcome back time for another dueling Excel podcast.
I'm bill Jelen from MrExcel.
Mike Girvin from Excel Is Fun.
Mike came up with the idea, today.
How do we generate a random date between two dates.
Now, I have to tell you, I'm in the process of rewriting all my books for Excel 2010.
So, I am the king of generating random data.
Right now for example, region let's say we had five regions equal R ampersand, Rand between comma 1 comma 5.
I'll just randomly generate some data down their, products.
How about equal CHAR rand between 65 that's an A and 69 that's going to give us products A through E and just to mix up a little bit ampersand rand between 11 comma 29.
There we go.
Miss those parentheses again.
I should use Mike's trick and making sure that I had the last one is the black parentheses, okay!
But dates are a little tough because with dates it would be nice, just to be able to type rand between 2/1/2008 but unfortunately that's how Excel displays the date.
That's not really how it's storing the date.
If I would press [ ctrl ] and the backwards accent.
The grub accent, you'll see that those dates are 39479, but who really knows, who knows that.
I mean that would be ridiculous.
I will just type that in.
So, I end up always using the helper cells up there equal rand between this first date, press [ F4 ] comma and then the second date and press [ F4 ].
Now, I'm gonna have to format that as a date.
So, [ ctrl + 1 ], choose date, choose the format that I link and double click to copy it down, alright.
But for me this is okay because I take the data, I convert it to values and those forms don't persist.
I don't have to keep these helper cells up here.
Cool trick though, if we wanted to keep these forms and we wanted to get rid of the helper cells up here in the formula bar.
I'm going to select C1 and press the [ F9 ] key.
[ F9 ] will convert that to the real value and then D1 and press the [ F9 ] key, that will also convert it to the real value and we'll copy that down and now we have a formula that does not rely on these helper cells up there.
So, there you have it.
That's my method.
I'll see what Mike has in store for us.
Mike: Thanks, MrExcel. Hey!
Randomizing dates, you do have to be a little bit careful.
Just because a date as MrExcel set is actually a serial number.
Now, if I were going to do region and product there's lots of ways to do.
So, another way is to use the index is that same trick, MrExcel highlighted the range, right here and hit [ F9 ] and then rand between 1 and 6.
So, we could just have some some words in essence.
We're looking up, and you could do the same thing for product here, but I only have five.
Now, date...
We'll use the same rand between but what about this if you don't have the date and cell, a helper cell and you don't have memorized the serial number.
Just use the date function.
Date needs a year month and a day, so I'm going to put 2008 comma the month is 2 comma the day is 1.
So that's February 1st, 2008.
I think that's a leap year.
I think, there was 29 days in that month.
Come on, then the top is going to be date 2008 comma 3 31.
Close parentheses, close parentheses.
So, that's another way and if you highlight these right here and [ F9 ], you can see they actually give you the serial number.
So, that's one nice use of the date function.
You can actually get the serial number.
Another way if you have an earlier version 2007 and you don't have the analysis tool pack added in under, tools add-in and you don't have the rand between.
You'd have to do something like this, how that equals date.
So, that's the starting point.
That's the minimum and we need to add to it some randomized number between 1 and 59 because there's 59 days between this date and the final date.
So, we want to use the INT function.
The INT takes the integer part, always going down of some number and then we use Rand.
Rand will generate a number between 0 and 1 and we're going to do, not 59.
So, we'll take Rand which generates the number between 0 and 1 but imagine if we got point 5 here right, point five times actually 60 would give us 30 days.
So, it add 30 days but the thing about rand is taking really close to zero, which means this can add zero which is fine because sometimes we hadn't want to add zero.
We want this bottom date, but on the top end we want to only add 59.
So, we actually added the extra number here.
That way we go from zero to 59 because it'll never quite get to 60.
Close parentheses and [ ctrl enter ] and there you have two formulas for randomizing dates between February 1st, 2008 and March 31st, 2008 all right, we'll see you next trick.
MrExcel: Okay! Now, Mike you confuse me on that one because you wanted numbers between 0 and 59 and so you asked for the Rand times 60, one more.
I had to come here to convince myself.
So, I wanted random numbers from 1 to 10 and I used Rand times 11 using your theory and sure enough we get numbers from 1 to 10.
But what I guess that I missed was that you also wanted a zero.
So, here we have some zero showing up as well.
So, if you use rand times 60, we're going to get 60 different numbers ranging from 0 up to 59.
So, if I really want random numbers from 1 to 10, then what I need to do is Rand times 10 and then at the end plus 1 and we'll copy that down.
Just have a little frequency distribution over here as I press [ F9 ].
You'll see that we are now getting numbers from 1 to 10.
So, Mike you're absolutely right.
Throw me for a little loop, how to come prove it to myself.
Why did I ever question it?
Hey, for everyone out there.
Thanks for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
