MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill Weekend Dates


November 06, 2017 - by Bill Jelen

Fill Weekend Dates

Excel Fill Weekend Dates. While Excel has a secret option to fill weekdays, there is no good way to fill weekend dates. This article will show you how.


Watch Video

  • Right-click the fill handle and drag to Fill Weekdays
  • But there is no similar option to fill weekends.
  • This could be useful for planning co-parenting schedules
  • Use a secret form of WorkDay.Intl
  • 7-digit binary string specifies which days 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 (backwards!?)

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2064 Phil weekend dates or every other
  • Wednesday hey welcome back to the
  • MrExcel on that cast I'm Bill Jelen well
  • its 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 sonars they're
  • spelled link right there and during that
  • seminar one of the things I usually do
  • is we control semicolon 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
  • show this cool trick where instead of
  • just grabbing the fill handle 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 all right
  • well I have the opposite problem I want
  • to fill weekends we can't they're
  • planning a co-parenting schedule alright
  • and as you'll all yeah that's going to
  • be tougher because there is no way to
  • fill weekend but there is this cool
  • function in Excel that we could use to
  • plan like sort of an employee calendar
  • where you say equal work day work day
  • from a start date go out one day know by
  • the way exclude these holidays I'm gonna
  • leave that one out right now all right
  • 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 work days monday through
  • friday which is kind of like Phil Phil
  • 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 equal
  • work day dot int L 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 right so if
  • we could just specify a weekend monday
  • tuesday wednesday thursday friday that
  • be exactly what we have received there
  • isn't a list here in the drop down for
  • this all right but here's the problem
  • the drop-down doesn't tell
  • story there's a secret way to do this
  • where for the weekend you specify a
  • seven character next string 7 characters
  • has to be ones or zeros the first
  • position is monday and tuesday and
  • wednesday then thursday and friday and
  • saturday and sunday this is a really
  • part weird part this is called weekend
  • right 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 all right so here
  • in quotes i'ma say for Monday no Tuesday
  • no once they know thursday no friday no
  • Sarah yes sunday yes all right I love
  • this the secret syntax that you will
  • never accidentally discover through the
  • truth a 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 days 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 zero and
  • we'll get every saturdays sunday and
  • wednesday yet another awesome cool use
  • for excel well that tip and 39 others
  • are in the book MrExcel excel 40
  • greatest hits of all time all right
  • episode wrap up we can usually right
  • click the filling and drag a date to
  • fill weekdays but there's no single
  • option to fill weekends this could be
  • useful for planning co-parenting
  • schedules there's a secret form of work
  • days out int l 7 digit binary string
  • specifies which day should appear on the
  • schedule left to right the digits
  • represent Monday through Sunday one
  • means don't count to stay 0 means do
  • include the stay which is sort of
  • backwards well there you have it while I
  • think 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 was saying it's time
  • for another netcast mr. exhale

Download File

Download the sample file here: Podcast2064.xlsm

Title Photo: Soledadsnp / Pixabay