There is a well-known trick in Excel to fill just weekdays. But what if your work week is not Monday through Friday? Today, a trick with WORKDAY.INTL in Excel to fill Monday, Wednesday, Friday.
Transcript of the video:
Learn Excel from Mr. Excel podcast, episode 2256. Fill Monday, Wednesday, Friday dates.
Hey welcome back to Mr. Excel netcast, I’m Bill Jelen.
This question from my Mobile, Alabama seminar, you know you can right click or right drag, right drag and say fill weekdays and it fills just the Monday ghd through Friday dates and some said, “Wait, I need to fill Monday, Wednesday, Friday.” Okay, So here’s my trick. I’m going to use network days on international that actually work date on international.
Both of these have the secret, undocumented weekend argument. So what I’m going to do is, I’m going to say equal workday international, start date, the date right above me. I want to go up one workday from there and then the weekend, see it’s not in this list here, you never know that this is here. The weekend is going to in quotes, has to be “7 binary digits” starting with Monday. Zero means it’s a weekend. So Monday, nil, not a weekend, Tuesday yes, weekend, Wednesday not a weekend, Thursday is a weekend, Friday, not a weekend, Saturday, Sunday like that and—oh, right answer, wrong format. It happens so many times in Excel. Alright, so jumps from Monday to Wednesday, I’ll just grab the phil handle and drag and it will give me all of the Monday, Wednesday, Friday dates, awesome little trick there with workday.intl.
Hey, I want to thank you for stopping by, I’ll see you next time for another netcast from Mr. Excel.
Hey welcome back to Mr. Excel netcast, I’m Bill Jelen.
This question from my Mobile, Alabama seminar, you know you can right click or right drag, right drag and say fill weekdays and it fills just the Monday ghd through Friday dates and some said, “Wait, I need to fill Monday, Wednesday, Friday.” Okay, So here’s my trick. I’m going to use network days on international that actually work date on international.
Both of these have the secret, undocumented weekend argument. So what I’m going to do is, I’m going to say equal workday international, start date, the date right above me. I want to go up one workday from there and then the weekend, see it’s not in this list here, you never know that this is here. The weekend is going to in quotes, has to be “7 binary digits” starting with Monday. Zero means it’s a weekend. So Monday, nil, not a weekend, Tuesday yes, weekend, Wednesday not a weekend, Thursday is a weekend, Friday, not a weekend, Saturday, Sunday like that and—oh, right answer, wrong format. It happens so many times in Excel. Alright, so jumps from Monday to Wednesday, I’ll just grab the phil handle and drag and it will give me all of the Monday, Wednesday, Friday dates, awesome little trick there with workday.intl.
Hey, I want to thank you for stopping by, I’ll see you next time for another netcast from Mr. Excel.
