A YouTube viewer wants to find a way to generate random weeks over the next 6 months. In Episode #1469, Bill "MrExcel" Jelen - using TODAY, WEEKDAY, and RANDBETWEEN Functions - shows us a Formula to calculate random weeks for the next 6 months or year.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1469: random weeks over the next six months.
Hey, welcome back to the MrExcel netcast.
Yes, it is Friday and that means that we should be doing a Dueling Excel podcast today.
Unfortunately, I'm in Florida for eight days and I’m recording way ahead and Mike was busy this week.
So, we just agreed not to do a duel this week.
We'll come back next week with a duel.
Today's question is sent in by-- from YouTube.
They want to find a way to generate random weeks over the next six months.
All right, so for me, the week-- I'm going to do a week starting, week starting.
I have a great little formula here that shows the current week's Monday.
So, always goes back to Monday of the current week.
The way that we do that is by using the WEEKDAY function, this really cool one here.
You know, they added a lot of things to the WEEKDAY function, but the best one of them all is number zero from Monday through 6 for Sunday.
So, you can always subtract the weekday up to today from today and it will get you back to Monday every time.
If it runs on Monday, it stays on Monday.
It doesn't go back to the previous Monday, so a really, really good way there to get back to the previous Monday.
Then, I want to choose any week; so any Monday over the next six weeks.
I'm going to say plus 7 times RANDBETWEEN 1 comma 26.
Copy that down how many random weeks we need.
These are the weeks that we're going to do.
Now, you'll see right there, there's a duplicate.
So, that is the problem with this.
You could end up with duplicates.
If you want, press F9 till all the duplicates go away or, you know, whatever you would like to do to deal with that.
The person also said, “Hey, I also need over the next six months or over the next year.” Well, hey, that's easy.
Next year just x 52 and you'll get random weeks over the next year.
So, kind of a cool little function there using the old, old WEEKDAY function with a comma 3.
Definitely my favorite one; always takes us back to a Monday date every single time.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1469: random weeks over the next six months.
Hey, welcome back to the MrExcel netcast.
Yes, it is Friday and that means that we should be doing a Dueling Excel podcast today.
Unfortunately, I'm in Florida for eight days and I’m recording way ahead and Mike was busy this week.
So, we just agreed not to do a duel this week.
We'll come back next week with a duel.
Today's question is sent in by-- from YouTube.
They want to find a way to generate random weeks over the next six months.
All right, so for me, the week-- I'm going to do a week starting, week starting.
I have a great little formula here that shows the current week's Monday.
So, always goes back to Monday of the current week.
The way that we do that is by using the WEEKDAY function, this really cool one here.
You know, they added a lot of things to the WEEKDAY function, but the best one of them all is number zero from Monday through 6 for Sunday.
So, you can always subtract the weekday up to today from today and it will get you back to Monday every time.
If it runs on Monday, it stays on Monday.
It doesn't go back to the previous Monday, so a really, really good way there to get back to the previous Monday.
Then, I want to choose any week; so any Monday over the next six weeks.
I'm going to say plus 7 times RANDBETWEEN 1 comma 26.
Copy that down how many random weeks we need.
These are the weeks that we're going to do.
Now, you'll see right there, there's a duplicate.
So, that is the problem with this.
You could end up with duplicates.
If you want, press F9 till all the duplicates go away or, you know, whatever you would like to do to deal with that.
The person also said, “Hey, I also need over the next six months or over the next year.” Well, hey, that's easy.
Next year just x 52 and you'll get random weeks over the next year.
So, kind of a cool little function there using the old, old WEEKDAY function with a comma 3.
Definitely my favorite one; always takes us back to a Monday date every single time.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
