Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
Go behind-the-scenes with racing's hottest, young talent, 17-year-old Dylan Kwasniewski, as he aspires to make it in the #1 motorsport in America – NASCAR
Mike and Bill offer different ways of generating a random date between two dates.
Tags:Excel Lesson: How to Generate Random Dates,Excel Random Data,Random date generation in Excel,Random numbers in Excel,excel,MrExcel,spreadsheets
Grab video code:
Bill: All right welcome back time for another dueling Excel podcast I’m Bill Jelen from MrExcel. I have Mike Kervin from Excel as Mike came up with the idea today how do we generate a random date between two dates? Now I have to tell you I am in the process of re writing all my books for excel 2010 so I am the king of generated random data right now. For example regions, let’s say we have five regions equal R and that ran between column one, column five. Now we just random that generate the data down there- products how about equals CHAR. Ran between 65 that’s an A and 69 that’s going to gives products A through E and just to mix up a little bit ampersand ran between 11 coma, 29.
Here we go just a little frenzies here again, I should use Mike’s trick and I am making sure that I have the last one is the black part to see. Okay but dates are little tough because with dates, it would be nice just to be able to type to ran 2001 and 2008 but unfortunately that is how excel displays the datas not really how it is storing the date. If I would press control and the backwards assets the grab asset you see that those dates are 39479. But who really knows, who knows that I mean that would be ridiculous just to be able to type that in. So I end up always using the help ourselves up there, equal ran between this first date, press f4 coma and then the second date and press F4.
Now I have to format that as a date so control 1, choose date, choose the format that I like and double click to copy it down. But for me this is okay because I take the data I converted to values and those formulas don’t persist I don’t have to keep this help ourselves up here. Cool trick though if we wanted to keep this formulas and we want to get rid of the help ourselves, up here on the formula bar I am 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 to the real value and copy that down. And now we have a formula that does not rely on this helpers ourselves out there.
So there you have it that’s my method I see what Mike has in stored for us.
Mike: Thanks MrExcel, hey randomizing dates to do have to be able to be careful just because a date as Mr Excel said is a serial number. Now if I were going to do region and product there’s lot of ways to do this.
Other ways use the index, use that same trick on Mr Excel highlighted the rains right here and hit F9 and then ran between one and six. So we could just have some word 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 ran between but what about this? If you don’t have the date and seller, helpers now and you don’t memorize the serial number, just use the date function.
Date needs a year, month and a day so I am going to put 2008 coma the month is two coma the day is one so that’s February 1st 2008. I think that is a leap year I think there was 29 days in that month. Coma and then the top is going to be date 2008 coma three 31 close parenthesis, so that’s another way. And if you highlight these right here min F9 you can see they actually give you the serial numbers 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 ran between. You have to do something like this –equals date.
So that’s the starting point, that’s the minimum and we need to add to it some randomized number between one and 59 because there’s 59 dates between this date and the final date. So we want to use the int function, the int takes integer part always going down of some number. And then we use ran will generate a number between zero and one, and we are going do not 59. So it will take ran which generates a number between zero and one, but if we got .5 here right .5 times actually 60 would give us 309 days, so we add 30 days.
But the thing about ran is that get really close to zero which means this can add zero which is fine. Because sometimes we want to add zero, we want this bottom date. But on the top end, we want to only add 59, so we actually added t eh extra number here that way we go from zero to 59. Because they will never quite get to a 60 close parenthesis and control enter. And there you have two formulas for randomizing dates between February 1st 2008 and March 31st 2008.
Alright we’ll see you next trick.
Bill: Okay Mike you confused me on that one because you wanted numbers between zero and 59 and so you asked for the run times 60, one more. And I can hear to convince myself so I want to random numbers one to 10 and I use ran times 11 using your theory. And sure enough we get numbers from one to 10 but what I guess that I missed was you also wanted a zero. So here we have some zero showing up as well. So if you use ran times 60 you’re going to get 60 different numbers ranging from zero up to 59. So if I really want random numbers from one to 10 then what I need to do is ran times 10 and at the end plus one and copy that down.
Step a little frequency distribution here as I press F9, you’ll see that we are now getting numbers from one to 10. So Mike you are absolutely right throw me for a little loop and I have to come to 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 Mr.Excel