This is one of the simple and yet powerful tips when working with Dates in Excel.
To calculate the number of workdays between two dates BUT EXCLUDING weekends (depending on your locale settings), we can use NETWORKDAYS(). In US and most western countries, Saturdays and Sundays would be excluded from the calculation.
Without changing your locale settings, NETWORKDAYS.INTL() calculates the number of working days between two dates (excludes Saturday and Sunday by default) but allows you to specify which days of the week are considered weekends.
The most powerful feature of both of these functions is that they can also exclude a list of holidays you specify. We’ll look into that as well.
First, let’s set the stage. You want to calculate exact number of days actually worked (for pay or efficiency or other KPIs), regardless of if they get paid on the weekends or holidays. You want the actual number of days worked…meaning, you want to take all the weekends and holidays into consideration and let Excel figure out the calculation.
So, let’s say we have a start date, and end date of an event/project, etc. We want to find the number of days between those days but without counting the weekends and holidays, if any happens to be in-between the start and end dates. For a larger range of days, you’d have to rack your brain to do this and inefficiently! However, with Excel’s function, it’s a piece of cake. Take the example below…first, we’ll just find the days without counting the weekends (we’ll tackle the holidays soon). The formula usage is as simple as any function can be…just 2 parameters!
That was too easy! Now, let’s also want to consider the holidays. So, we have a small table of holidays in column G below. So, by passing its range as the optional 3rd parameter to the function, we can also exclude the holidays. 
Do you see the difference?
NOTE: For countries (especially in various non-Christian countries), Friday and Sunday are the weekends, and of course, the holidays would be different from ours. In that case, using NETWORKDAYS.INTL() we could pass it a range of dates for weekends (if non-consecutive), or if consecutive 2 days of weekends then can pass 7 as the starting weekend (for Friday, for example), and for holidays, another custom range of values as we did for NETWORKDAYS() above.
Now, what would happen if you just deducted the dates using a formula such as =B25-A25? Why does it show 12/1/2019? What happens if you change the data type to General or Number? Why is that a “weird” number?
Actually, that weird number is a unique serial number Excel uses for that date. To deduct dates, we can’t simply deduct as if they’re numeric numbers (unless, you take 2 serial numbers, find the diff, then convert it to Date type…which will work). Instead, you want to use the appropriate Date formulas…of which there are many for different purposes. I will demonstrate some of the usages in the future blog.
Cheers.
