Look Who it Was Random video

Excel - Dueling Excel: How to Generate Random Dates in Excel | Excel Tips and Tricks - Episode 1095

1,633 views · Published 4 September 2009 · 6:43 · Indexed 21 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Dueling Excel: How to Generate Random Dates in Excel | Excel Tips and Tricks.

Welcome to another exciting episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel.com and I'm here with Mike Girvin from Excel Is Fun on YouTube. Today, Mike came up with a great idea for us to tackle - how to generate a random date between two given dates in Excel. As the king of generating random data, I'm excited to share my method with you.

As I am currently in the process of rewriting all my books for Excel 2010, I have become quite familiar with generating random data. For example, to generate random regions, I simply use the formula "R & Randbetween(1,5)" and for products, I use "CHAR(Randbetween(65,69)) & Randbetween(11,29)". However, generating random dates is a bit trickier as Excel stores dates as serial numbers. But don't worry, I have a solution for you.

My method involves using helper cells and the Randbetween function. I simply select the first date, press F4, then select the second date and press F4 again. Then, I format the cells as dates and copy the formula down. This works well for me as I can easily convert the data to values and get rid of the helper cells. But, Mike has a different approach that doesn't require helper cells at all.

Mike's method involves using the Date function. He simply inputs the year, month, and day for the first and last date and then uses the Randbetween function to generate a random number between 0 and 1. By using the INT function, he ensures that the number is always rounded down to an integer. This way, he can add a random number of days to the first date to get a random date between the two given dates.

But what if you don't have the Analysis ToolPak installed and can't use the Randbetween function? Don't worry, Mike has a solution for that too. He uses the Date function again, but this time he adds a random number between 0 and 59 to the first date. This ensures that the final date will always be within the range of the two given dates. And just to make sure, he also adds 1 to the formula to include the first date in the range.

So there you have it, two different methods for generating random dates in Excel. Whether you prefer using helper cells or not, we've got you covered. Thanks for tuning in to another episode of the Dueling Excel podcast. Don't forget to subscribe to both MrExcel and Excel Is Fun for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Random Dates in Excel
(00:33) Discussion on generating random dates 
(01:20) Bill demonstrates his method using helper cells 
(02:04) Bill shows a trick to convert helper cells into formulas 
(02:39) Mike presents his method using the INDEX function 
(03:51) Mike shows another method using the DATE function 
(05:24) Mike explains his formula for generating random numbers 
(06:23) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Adding random numbers in Excel
Converting formulas to values
Excel tips
Formatting dates in Excel
Frequency distribution in Excel
Generating random dates
Random data
Randomizing dates in Excel
Using helper cells in Excel
Using the date function in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152599/


Mike and Bill offer different ways of generating a random date between two dates. Episode 1095 shows you how.Clicking Like really helps the algorithm

More from this channel