Fill Weekend Dates

Learn Excel from MrExcel Podcast, Episode 2064: Fill Weekend Dates or Every Other Wednesday

Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.  Well, it’s Spring which means that the Spring seminar season is in full bloom.   I was just in Knoxville, Tennessee yesterday, great crowd of 85 people there.  If you happen to be near Columbus or Bloomington or Indianapolis or Sarasota, please come check out the seminars.  There’s the link right there.

And during that seminar, one of the things I usually do is where Ctrl+; puts in the current date.  We can format that as a long date.  So we can see that today is Friday, how can I forget that.  And then, I showed this cool trick where instead of just grabbing the fill handle and dragging and getting all the days like that, I right-click the fill handle, and when I let go, I choose Fill Weekdays which fills just the Monday through Friday days, alright?   So, that's the trick that I show all the time and then someone came up at one of the practices and said, “Hi.  Well, I have the opposite problem.  I want to fill weekends.”  Weekends.  They're planning a co-parenting schedule.  Alright, and I said, “Well, yeah, that's going to be tougher because there is no way to fill weekends.” But there is this cool function in Excel that we could use to plan, like sort of an employee calendar, where you say =WORKDAY, =WORKDAY from a start date, go out 1 day.  And oh, by the way, exclude these holidays.  I'm going to leave that one out right now.  Alright, now that's the right answer on the wrong format.  You have to make sure to format that, and it will successfully give you a list of all the workdays, Monday through Friday which is kind of like Fill- Fill weekdays, but it's a formula based way to do that.  But then, you know, there's some countries where the weekend isn't Saturday and Sunday, so they created an international version of this.  So =WORKDAY.INTL.  Here's the start date, here's the number of days just the next day on the schedule, and then we get to specify what the weekend is, alright?  So, if we could just specify a weekend of Monday, Tuesday, Wednesday, Thursday, Friday, that would be exactly what we have.  But you see, there isn't a list here in the drop-down for this, alright. 

But here's the problem, the drop-down doesn't tell the whole story.  There's a secret way to do this where for the weekend you specify a 7-character text string, 7 characters has to be ones or zeros.  The first position is Monday then Tuesday then Wednesday then Thursday and Friday then Saturday then Sunday.  Now, this is the really part- weird part, this is called weekend, alright?  And so a 1 means don't include this day in your schedule, a 0 means include the standard schedule.  It's really kind of backwards from what you would think until you realize that it's like a double negative.  Alright, so here in quotes I'm going to say, for Monday- no, Tuesday – no, Wednesday – no, Thursday – no, Friday – no, Saturday – yes, Sunday – yes.  Alright, I love this, the secret syntax that you will never accidentally discover through the- through the little drop-down there.  You have to go into Excel Help. Or, hey, read one of my books that documents- documents that. 

Okay, so there we go with the Saturday and Sunday dates.  If we needed Saturdays, Sundays and Wednesdays, we just come here.  This is Monday, that's Tuesday, change that Wednesday from a 1 to a 0, and we'll get every Saturday, Sunday and Wednesday. Ahh, yet another awesome, cool use for Excel.

Well, that tip and 39 others are in the book, MrExcel XL, The 40 Greatest Tips of All Time.

Alright, episode wrap up:  We can usually right-click the fill handle and drag a date to Fill Weekdays but there's no similar option to fill weekends.  This could be useful for planning co-parenting schedules.  There's a secret form of WorkDay.Intl, 7-digit binary string specifies which day should appear on the schedule.  Left to right, the digits represent Monday through Sunday.  1 means don't count this day, 0 means do include this day, which is sort of backwards.

Ahh, well, there you have it.  Well, I want to thank everyone who stopped into my live seminar in Knoxville yesterday, we had a great crowd.  And I want to thank you for watching this video.  We’ll see you next time for another netcast for MrExcel.