To get started, let’s first set up some dates, so we have a visual representation to refer to. In C5, I’ll add a start date. Then, I’ll add and copy a formula below that simply adds “1” to each date above. The result is that we get a list of 14 consecutive dates. Now let’s show the day of the week for each date. There are several ways we can do this. First, I could apply a custom format to column C that includes the day of the week along with the date. This works fine, but let’s look at a couple ways we can add a day of the week in column B. First, I can add a formula in B that simply points to the existing date in Column C. Then I can format this date with another custom format. This time, I’ll set the format to only show the “day of week.” Another way we can show the day of the week is to use the TEXT function and just specify the custom format we need. In this case, we end up with just text. Now that we can see the day of week along with the date, let’s calculate the days between the first and last date. Because dates are serial numbers in Excel, we can simply subtract the start date from the end date. The result is 13. Looking at this result, keep in mind that 13 is one less than the total dates in this list. If you need a result that gives you 14 days total, effectively including both the start and end date, then adjust the formula to add “1”. What if you just want to count workdays, excluding weekends? In that case, you can use the NETWORKDAYS function. NETWORKDAYS takes three arguments: a start date, an end date, and an optional argument called “holidays.” In this first example, we’ll leave holidays out. This gives us ten working days. If we count workdays by selecting cells in the list, we can see the same result in the status bar. What if you want to exclude holidays? In that case, we need to add the “holidays” argument along with the same start and end dates. For holidays, just select the range that includes the holiday dates; the holiday names don’t matter. Now we get a result of seven working days from December 20th through January 2nd. If I remove one of the holidays in the list, the number increases by one. And, if I change the start date to November 1st, we get ten working days for both formulas, since there are no holidays in our list between November 1st and November 14th.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
