NETWORKDAYS: Calculating working days
Video: NETWORKDAYS: Calculating working daysCalculating differences between dates in Excel is often very straightforward. It's simply a matter of subtracting information from two different cells. So in Cell D4 here, we just simply want to find out the amount of time elapsed from a starting date to an ending date here. Equal the later date minus the earlier date, 170 days. So that tells us a lot. But in a working environment sometimes we want to know the actual working days between the two dates.
Viewers: in countries Watching now:
In Excel 2010: Working with Dates and Times, Dennis Taylor shares his solutions for optimizing the use of dates and times in Excel 2010. This course explains what's going on behind the scenes when Excel stores dates and times, gives tips for entering dates and times, and shows options for date and time formatting. It also demonstrates the various date and time functions and shows how to calculate with dates and times in a range of scenarios. Exercise files are included with the course.
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data
NETWORKDAYS: Calculating working days
Calculating differences between dates in Excel is often very straightforward. It's simply a matter of subtracting information from two different cells. So in Cell D4 here, we just simply want to find out the amount of time elapsed from a starting date to an ending date here. Equal the later date minus the earlier date, 170 days. So that tells us a lot. But in a working environment sometimes we want to know the actual working days between the two dates.
So here's the same data here. Here we want to use the function =NETWORKDAYS. Now as I'm typing this, this is a long entry or longer than some. I see it right here. So I'll just click on it. Press the tab key. It pops into place. Starting date right here, comma, ending date. For the moment, we'll ignore holidays. It's not going to be 170. This function will not count Saturdays and Sundays. So our answer is 123, quite a bit pure than the 170 that we see over here.
But we do have holidays involved here. And of course, over this time period we've got let July 4th and Labor Day and Thanksgiving. The holidays are listed over here, fairly comprehensive. Let's do the same thing all over again. I'm just going to copy this function over here. And let's jump in and make an adjustment. NETWORKDAYS does allow us to also not include those holidays. Now one bit of information here too. In the year 2010, July 4th is on a Sunday. So it's not going to be discounted twice, because it's a weekend it will be counted, but July 5th, which is Monday, is counted as a holiday.
Now we can pick up the whole list here. It's okay if we include those that are beyond the range. That won't hurt anything. So if we don't count the holidays here, how many working days do we have? 119, four fewer. It's important to note that in both of these examples with NETWORDAYS too, that the starting date and the ending date are both calculated in the example. So if this happens to represent a project and it starts on noon on a certain day and ends on noon on another day, then you'd want to subtract 1 from this answer.
Most of the time that's not a major concern, but do be alert to that if it's a shorter timeframe. Both dates are counted. So the function NETWORKDAYS is a great tool for calculating differences of working days between two dates.
There are currently no FAQs about Excel 2010: Working with Dates and Times.