How to generate random dates in Excel.
How to generate random date and time in Excel.
Table of Contents
(0:00) Welcome
(0:34) Random Date in Excel
(2:38) Random Time in Excel
(3:01) RandomTime During the Workday in Excel
(3:58) Random Time to nearest quarter hour
(4:45) Random Date /Time in Excel
(5:36) Random Weekday in 2025 in Excel
(6:44) Random Weekday No Holidays in Excel
(7:45) Randomly Choose a Monday or Thursday in Excel
(9:19) Randomly Choose particular weekdays in Excel
(10:35) Wrap-Up
How to generate random date and time in Excel.
Table of Contents
(0:00) Welcome
(0:34) Random Date in Excel
(2:38) Random Time in Excel
(3:01) RandomTime During the Workday in Excel
(3:58) Random Time to nearest quarter hour
(4:45) Random Date /Time in Excel
(5:36) Random Weekday in 2025 in Excel
(6:44) Random Weekday No Holidays in Excel
(7:45) Randomly Choose a Monday or Thursday in Excel
(9:19) Randomly Choose particular weekdays in Excel
(10:35) Wrap-Up
Transcript of the video:
In Excel, create random dates or random date or time.
Yesterday I was working on the list of most common Excel questions in Google.
Trying to do some shorts. And there's shorts out there for random yes or no.
Random sample from a data set. Or random sort in Excel.
But then I ran up against this series of questions.
And I realized I could not do it in less than 60 seconds, so hence a long-form video.
We'll be answering all of these questions here.
Use the little chapter tabs below the video to jump to the right spot.
All right, let's just start with Choose a date in Excel between two dates.
Put the start date here, the end date here, and the method I use all the time is RANDBETWEEN.
From the start date, I'll press F4 to lock that down.
And then from the end date, press F4 to lock that down.
Now with this method, we're going to have to format the answer.
We're going to get a serial number instead of a date.
So don't be freaked out there.
That number in the 40,000 to 50,000 range is the right answer. It's just not formatted correctly.
So we choose short date and we get July 13th. Double click and copy that down.
And we have a series of random dates between January 1st and December 31st of 2025.
People always point out that you can actually put those dates right in the formula like this.
You have to put them in quotes, “1/1/2025”.
And of course, if you're anywhere outside the United States where they don't put the month first, you'll have to reverse this next one.
I put “12/31/2025”, but the rest of the world is going to put “31/12/2025”.
Still has to be formatted just like the previous one.
Double-click and copy that down.
The third method, and I like this one because we don't have to format it. So we just start using the DATE function.
And it's going to be in 2025. Starting on January.
But then for the day, we're going to give it a random day between one and 365.
So we use RANDBETWEEN here of 1 comma 365. Close the parenthesis for the RANDBETWEEN.
Close the parenthesis for the DATE. See it's already formatted as a day.
Double-click and copy that down.
Method two, it's going to have problems if they open that workbook in a country where it's not month, day, year.
So there's a lot of benefits to either method one or method three.
All right, then randomly choosing times in Excel.
Well, on the face of it, it's really easy, it's just the RAND() function.
We'll return a decimal number. And when we format that as a time.
Double-click and copy it down. Very easy way to go.
But I'm not sure what was the point of this question?
They're probably trying to choose a random time during the workday.
Or something like that.
So maybe you want to choose the time from 8:00 AM to 4:59 PM, that's nine hours.
Nine times six is 54, so there's 540 minutes there. So we can use equal TIME(.
What hour? Zero hour.
What minute?
That's where we're putting in the RANDBETWEEN(0,539).
And then I'm just going to always have it be at the top of the minute. No seconds.
But you could here put in RANDBETWEEN(0,59). But I'm not going to do that.
I'm just going to be happy to choose a random minute.
Format that as a time.
Actually, no, it'll already be formatted as a time because I know it from the TIME function. Double click and copy that down.
But then I'm starting to think what would I really be doing?
I'd be trying to choose, like maybe I have appointments.
I have people coming in and I'm trying to assign appointments or something like that.
So I want it to be on a quarter hour.
In nine hours there are, nine times four, 36 quarter hours.
So we'll start at eight o'clock, and then for the minute, we'll do RANDBETWEEN(0,35) times 15.
That means that the first appointment of the day will be at 8:00 AM, the last appointment of the day will be at 4:45 PM.
Sorry, no one gets a lunch. Comma zero for the seconds.
Double-click and copy that down.
And now we always have something on the quarter hour. Then randomly choose a date and time.
Well, hey, here we go.
You can use any formula from back here, plus any formula from here.
So I'm going to do this, equal DATE of 2025, first month, and then which day?
RANDBETWEEN(1,365) and if this was a leap year, use 366, plus RAND.
Now you're going to have to go into the number format here.
Choose date.
And near the bottom is the one that shows date and time. So either that one or that one.
Click okay. Double-click and copy that down.
And again, you can use anything you want before the plus from earlier in the video.
Anything you want after the plus from this time worksheet.
What if we want to limit this to just Monday through Friday dates?
Beautiful function, it returns Monday through Friday, the NETWORKDAYS and the WORKDAY function.
So here's our start date, I left New Year's Day out.
Here's our end date, I left New Year's Eve out.
The number of days between that date and that date, inclusive, so this counts the first day and the last day is 259.
So here's what we're going to do, we're going to start with the WORKDAY function.
Starting from the earliest date, we'll press F4 to lock that down.
And then how many days out do we want to go?
It's going to be a random number between zero and this number, 259, again, I'll press F4, minus one.
Close paren, and then close the paren for the WORKDAY.
All right, right answer, wrong format. Come up here.
We'll choose Long Date. Just so we can see what we get.
Double-click and copy that down.
Right, and you see it's always Monday through Friday. But we don't want any appointments on holidays.
So out here, somewhere in a hidden area of the spreadsheet, put a list of the company holidays.
Now that forces that us to change the NETWORKDAYS days function.
That an optional third argument with a list of holidays.
So while in the previous example, there were 259 workdays.
Once we take those holidays out, we're at 248.
So then equal Workday, starting from the earliest day, F4, comma, RANDBETWEEN, 0,248, again, press F4, minus one.
And then a list of the company holidays, we'll press F4 there.
If I named that range, holidays, I wouldn't have to press F4.
And format that as a long date, double click and copy that down.
And we should have nothing that ends up on a holiday like Christmas or 4th of July.
Let's just keep going. What if we just want a random Monday or Thursday?
Now we're going to switch over to the INTL version of NETWORKDAYS because it allows this awesome weekend argument.
And most people choose from the list for a weekend.
But what's really cool is we can pass it a seven digit binary string. And again, the question here is, is it a weekend?
So for Monday, it's not a weekend, that means we're open on Mondays.
But Tuesday and Wednesday are going to be treated as weekends, no appointments those days.
Thursday, not a weekend. Friday, Saturday, Sunday are weekends.
So depending on the combination of zeros and ones you put in here, it'll make sure that the resulting date always ends up on, in this case, Monday or Thursday.
So there's 96 of those dates. Equal WORKDAY.INTL, starting from the earliest date, F4.
How many days out? RANDBETWEEN 0,96, F4, minus one.
The weekend is open on Monday, closed Tuesday, Wednesday, Thursday we're open, Friday, Saturday, Sunday we're closed.
And then specify the list of company holidays, press F4, closing paren.
Format that as a long date, double click and copy that down.
And we get just Monday or Thursday dates. Every time you press F9, a new set of dates.
If you really want to customize this.
Out here, I have a little list of the weekday values. Put that in a pivot table.
The reason I put it in a pivot table is just simply to be able to get this slicer.
And then as I choose from the slicer, you see that the pivot table updates.
Here, I have a COUNTIF formula to count how many times Monday appears.
I actually do the COUNTIF and then subtract it from one.
So that way in essence, we're closed on Monday, open on Tuesday, closed Wednesday through Sunday.
A little CONCATenation here. And then I've named that to be Weekend.
So check out these formulas.
If we're just choosing appointments on Tuesdays, there's going to be 51 between those two dates that don't fall on a holiday.
And then down here, our formula is going to randomly choose Tuesdays.
If I turn on the multi-select in Tuesdays and Thursdays.
There's a bunch of Tuesdays and Thursdays.
Clear all this. If I just want Friday.
Clear all this.
If I just want Saturday, and Control+click on Sunday, then I get just weekend dates.
This is the ultimate flexibility as far as which days there are.
What sounded like such a simple question, but clearly can't be done in less than 60 seconds, not answering all of these questions.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Yesterday I was working on the list of most common Excel questions in Google.
Trying to do some shorts. And there's shorts out there for random yes or no.
Random sample from a data set. Or random sort in Excel.
But then I ran up against this series of questions.
And I realized I could not do it in less than 60 seconds, so hence a long-form video.
We'll be answering all of these questions here.
Use the little chapter tabs below the video to jump to the right spot.
All right, let's just start with Choose a date in Excel between two dates.
Put the start date here, the end date here, and the method I use all the time is RANDBETWEEN.
From the start date, I'll press F4 to lock that down.
And then from the end date, press F4 to lock that down.
Now with this method, we're going to have to format the answer.
We're going to get a serial number instead of a date.
So don't be freaked out there.
That number in the 40,000 to 50,000 range is the right answer. It's just not formatted correctly.
So we choose short date and we get July 13th. Double click and copy that down.
And we have a series of random dates between January 1st and December 31st of 2025.
People always point out that you can actually put those dates right in the formula like this.
You have to put them in quotes, “1/1/2025”.
And of course, if you're anywhere outside the United States where they don't put the month first, you'll have to reverse this next one.
I put “12/31/2025”, but the rest of the world is going to put “31/12/2025”.
Still has to be formatted just like the previous one.
Double-click and copy that down.
The third method, and I like this one because we don't have to format it. So we just start using the DATE function.
And it's going to be in 2025. Starting on January.
But then for the day, we're going to give it a random day between one and 365.
So we use RANDBETWEEN here of 1 comma 365. Close the parenthesis for the RANDBETWEEN.
Close the parenthesis for the DATE. See it's already formatted as a day.
Double-click and copy that down.
Method two, it's going to have problems if they open that workbook in a country where it's not month, day, year.
So there's a lot of benefits to either method one or method three.
All right, then randomly choosing times in Excel.
Well, on the face of it, it's really easy, it's just the RAND() function.
We'll return a decimal number. And when we format that as a time.
Double-click and copy it down. Very easy way to go.
But I'm not sure what was the point of this question?
They're probably trying to choose a random time during the workday.
Or something like that.
So maybe you want to choose the time from 8:00 AM to 4:59 PM, that's nine hours.
Nine times six is 54, so there's 540 minutes there. So we can use equal TIME(.
What hour? Zero hour.
What minute?
That's where we're putting in the RANDBETWEEN(0,539).
And then I'm just going to always have it be at the top of the minute. No seconds.
But you could here put in RANDBETWEEN(0,59). But I'm not going to do that.
I'm just going to be happy to choose a random minute.
Format that as a time.
Actually, no, it'll already be formatted as a time because I know it from the TIME function. Double click and copy that down.
But then I'm starting to think what would I really be doing?
I'd be trying to choose, like maybe I have appointments.
I have people coming in and I'm trying to assign appointments or something like that.
So I want it to be on a quarter hour.
In nine hours there are, nine times four, 36 quarter hours.
So we'll start at eight o'clock, and then for the minute, we'll do RANDBETWEEN(0,35) times 15.
That means that the first appointment of the day will be at 8:00 AM, the last appointment of the day will be at 4:45 PM.
Sorry, no one gets a lunch. Comma zero for the seconds.
Double-click and copy that down.
And now we always have something on the quarter hour. Then randomly choose a date and time.
Well, hey, here we go.
You can use any formula from back here, plus any formula from here.
So I'm going to do this, equal DATE of 2025, first month, and then which day?
RANDBETWEEN(1,365) and if this was a leap year, use 366, plus RAND.
Now you're going to have to go into the number format here.
Choose date.
And near the bottom is the one that shows date and time. So either that one or that one.
Click okay. Double-click and copy that down.
And again, you can use anything you want before the plus from earlier in the video.
Anything you want after the plus from this time worksheet.
What if we want to limit this to just Monday through Friday dates?
Beautiful function, it returns Monday through Friday, the NETWORKDAYS and the WORKDAY function.
So here's our start date, I left New Year's Day out.
Here's our end date, I left New Year's Eve out.
The number of days between that date and that date, inclusive, so this counts the first day and the last day is 259.
So here's what we're going to do, we're going to start with the WORKDAY function.
Starting from the earliest date, we'll press F4 to lock that down.
And then how many days out do we want to go?
It's going to be a random number between zero and this number, 259, again, I'll press F4, minus one.
Close paren, and then close the paren for the WORKDAY.
All right, right answer, wrong format. Come up here.
We'll choose Long Date. Just so we can see what we get.
Double-click and copy that down.
Right, and you see it's always Monday through Friday. But we don't want any appointments on holidays.
So out here, somewhere in a hidden area of the spreadsheet, put a list of the company holidays.
Now that forces that us to change the NETWORKDAYS days function.
That an optional third argument with a list of holidays.
So while in the previous example, there were 259 workdays.
Once we take those holidays out, we're at 248.
So then equal Workday, starting from the earliest day, F4, comma, RANDBETWEEN, 0,248, again, press F4, minus one.
And then a list of the company holidays, we'll press F4 there.
If I named that range, holidays, I wouldn't have to press F4.
And format that as a long date, double click and copy that down.
And we should have nothing that ends up on a holiday like Christmas or 4th of July.
Let's just keep going. What if we just want a random Monday or Thursday?
Now we're going to switch over to the INTL version of NETWORKDAYS because it allows this awesome weekend argument.
And most people choose from the list for a weekend.
But what's really cool is we can pass it a seven digit binary string. And again, the question here is, is it a weekend?
So for Monday, it's not a weekend, that means we're open on Mondays.
But Tuesday and Wednesday are going to be treated as weekends, no appointments those days.
Thursday, not a weekend. Friday, Saturday, Sunday are weekends.
So depending on the combination of zeros and ones you put in here, it'll make sure that the resulting date always ends up on, in this case, Monday or Thursday.
So there's 96 of those dates. Equal WORKDAY.INTL, starting from the earliest date, F4.
How many days out? RANDBETWEEN 0,96, F4, minus one.
The weekend is open on Monday, closed Tuesday, Wednesday, Thursday we're open, Friday, Saturday, Sunday we're closed.
And then specify the list of company holidays, press F4, closing paren.
Format that as a long date, double click and copy that down.
And we get just Monday or Thursday dates. Every time you press F9, a new set of dates.
If you really want to customize this.
Out here, I have a little list of the weekday values. Put that in a pivot table.
The reason I put it in a pivot table is just simply to be able to get this slicer.
And then as I choose from the slicer, you see that the pivot table updates.
Here, I have a COUNTIF formula to count how many times Monday appears.
I actually do the COUNTIF and then subtract it from one.
So that way in essence, we're closed on Monday, open on Tuesday, closed Wednesday through Sunday.
A little CONCATenation here. And then I've named that to be Weekend.
So check out these formulas.
If we're just choosing appointments on Tuesdays, there's going to be 51 between those two dates that don't fall on a holiday.
And then down here, our formula is going to randomly choose Tuesdays.
If I turn on the multi-select in Tuesdays and Thursdays.
There's a bunch of Tuesdays and Thursdays.
Clear all this. If I just want Friday.
Clear all this.
If I just want Saturday, and Control+click on Sunday, then I get just weekend dates.
This is the ultimate flexibility as far as which days there are.
What sounded like such a simple question, but clearly can't be done in less than 60 seconds, not answering all of these questions.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
