Workplace Calendar

Learn Excel from MrExcel podcast, episode 2024 - Workplace Calendar Using WORKDAY.INTL!

Alright, I'm podcasting this entire book, go ahead and subscribe to the playlist, that “i” on the top-right hand corner will take you there!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Now yesterday we talked about using NETWORKDAYS.INTL, either with this binary code, or with just the Sunday-only, at a list of holidays, to calculate the number of workdays between a start date and an end date. Today we're going to use a complementary function called WORKDAY, alright. So here we have someone who's on probation for 30 workdays, they start on this state, they're going to go out 30 days. So =WORKDAY from the start date, going out that many days, ignoring these holidays, press Enter, right answer, wrong format, why can't they get this one right still? Alright, and it calculates the workday where this person is off probation. I've never seen anyone do this, it's silly, you're not sure the guy's going to work every day, you know, it doesn't work, but here's where this really can come in handy.

So going back to the Hartville Marketplace and Flea Market, they're open Monday, Thursday, Friday, Saturday. So here's the first day of their season, Monday April 30th- this is some other farm market, because this place is open all the time. If we want to calculate the next day they're open, right, so if it's Monday, I want to calculate the Thursday, if it's Thursday it'll be Friday, if it's Friday it'll be Saturday, Saturday it'll be two days later on Monday, there are no holidays, they're open all the time. What we can do, and you know what, I'm lazy, here let me just take FORMULATEXT and grab this formula, and then Ctrl+C, Alt S V to change it into values, alright.

So, first thing, it's not plural, so you have to take off the S and get rid of the NET, alright, so WORKDAY.INTL. Start from A8 to cell above me, we're going out one day, and then the rest of it is the same, the weekend, I can get it to be the binary string or the value, and then the holidays if you have that. Alright, and so then, from Monday it's going to calculate Thursday, and then I'll just copy that formula down, Paste Special Formulas, that was Alt E S F Enter, and it will calculate the next day, the next day, and so on.

For the manufacturing plant that's open 6 days a week, they are only closed on Sundays, so I'll just start from the same day, and let me grab the list of holidays. So here we'll do =WORKDAY.INTL, start date, go out one day, the weekend is going to be Sunday-only, and our list of holidays out of here, don't forget to press F4 to lock that down. Format as a Short Date, and then copy down, and what you'll see is any Sunday, so right there, the 6th was skipped, when we get out to Memorial Day, we worked the 26th, 27th and 28th were skipped. Alright, great way to build an employee calendar. This tip is just one of the bonus tips, right there between tip 29 and tip 30, 40 tips plus a few more I'm sure, buy the whole book, click the “i” on the top-right hand corner.

So yesterday in podcast 2023 we introduced NETWORKDAYS and NETWORKDAYS.INTL, there's a pair of similar functions, WORKDAY, not plural, WORKDAY.INTL, to calculate an ending date. So the start date, number of workdays out, we can type in holidays, calculates the end date, 30-day probation, but no one really does that. It’s just great for building an employee schedule or employee calendar, we're just calculating the next workday, but the first start date at WORKDAY, drag that formula down to build the schedule.

Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!