Join Dennis Taylor for an in-depth discussion in this video NETWORKDAYS.INTL and WORKDAY.INTL, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: NETWORKDAYS.INTL and WORKDAY.INTL