NETWORKDAYS.INTL and WORKDAY.INTL

- [Instructor] If you're trying to determine the length of a project in the past or in the future, in terms of number of days, the two functions you want to be considering are net workdays and workday, and each of them has a companion with the suffix intl, meaning international. Let's first look at net workdays. If we were simply calculating the timing left between December 4th of 2017 and February 2nd of 2018, we could simply subtract the two. Equal the later date minus the earlier date is 60 days.

But there were weekends there, let's not count them, so let's use the function net workdays. It's a long function. As soon as you see it in the list, you can double click it. Notice the two variations here. I'll use the simple version first, and first click on start date, comma, end date, and if we don't want to count those weekends, automatically we just press enter, and instead of 60 days, it's 45 days. But there also is the option for taking into account holidays, and there were quite a few in that timeframe, and they're displayed over in column A.

After the end date, let's put in a comma, and highlight the relevant dates, and it's okay if we include a few more that are not really in there. Enter, 40 days. Now, what if you've got an unusual work week pattern? Maybe you work Monday through Thursday, or maybe you work Tuesday through Friday, or maybe you're in a different culture, where the weekend is Friday, Saturday instead of Saturday, Sunday. If you have any of those needs, you want to be using the other function, networkdays.intl. Let's use it right here. Once again, equal n, we see it right away.

This is the longer one. We'll use this. If there's nothing unusual about your work week, in other words, if it's a Monday through Friday and weekends are Saturday, Sunday, you wouldn't even need to use this function. This too starts off in the same way. You've got a starting date, comma, an ending date, comma, and now look what we see. All kinds of different two-day patterns for the weekend, and all one days listed singly, and if for example, you do have a weekend that's Friday, Saturday, that's your work week, then let's put this in.

We can double click it or simply type in a seven, comma, and then let's go highlight the relevant holidays, and enter, so it's 39 days. Many times these are likely to be close, but they're different in this case, not always different. I'm going to drag this over to the right because there could be another variation. What if you work a week that's Monday through Thursday, or Tuesday through Saturday? Now down below here, I want to make visible, I've got a box that shows that we can put in what's called a text string of seven characters consisting of zeroes and ones.

I'll use it here in the formula. It will appear right after or in place of, in this case, the seven. Now to get that out of there and expose these entries, there it is again, we don't see, and we won't see on the screen, anything about that seven-letter code, but I'm going to put it in right now, double quote, and this is for the person who works Monday through Thursday, four zeroes. Each of these is a working day and it starts on Monday, so Monday, Tuesday, Wednesday, Thursday, and then the weekend, the three-day weekend for this particular working pattern, Friday, Saturday, Sunday, within double quotes, and enter, 36 days.

Remember, this person is only working a four-day week. Now presumably the days are longer, and you might want to experiment with the hours here, multiply them by eight, and then possibly multiply them by 10, that sort of thing, to figure out the length of a project. That's a different issue. I'm going to drag this to the right and adjust the format. Let's make a change here. The holidays, of course, are not in column C here. They're over in column A. But if the working pattern is Monday is off, that's going to be a one, and then Tuesday, Wednesday, Thursday, Friday.

So for someone who works Tuesday through Friday, this would be the binary pattern that we see here, is using this set of characters here between the double quotes. Now, similar, and I won't go through all the variations, the other function that you use when perhaps looking ahead, a starting date of May 9th. This is a project that's 60 days long, let's say. The function here is workday. And here too, we've got the international variation, so I'll just show the international variation here. Let's imagine you work in an environment where the weekend is Friday, Saturday.

You've got a project that starts on May 9th, comma. Here's the length of it, 60 days, comma, and what's your working pattern? Friday, Saturday is the weekend, so you use that one, number seven again, comma, and are there holidays involved? We don't necessarily know which ones, but at least some of these would be involved. Probably not all of those, but nevertheless, we will get an answer here of a completion date. Or do we? Any time you're working with the dates and you see numbers like this, remember it hasn't been formatted yet, we can simply copy the format from above.

I'm going to hold down the right mouse button, drag this cell down, let go of the right mouse button, copy here as formats only. So that project that begins on May 9th, it's a 60-day project, for a person who has a weekend alignment of Friday and Saturday. That project will be finished on August 5th. So different approaches here for calculating unusual workday patterns using the workday function in this example, and then earlier we saw how to use the same capability with a function called networkdays.intl.