Скачать или смотреть How to Count Business Days between Two Dates in Excel with an IF Statement

Learn how to use Excel's NETWORKDAYS function along with the IF statement to count business days excluding holidays, even when the end date is blank.
---
This video is based on the question https://stackoverflow.com/q/71152262/ asked by the user 'Katrina' ( https://stackoverflow.com/u/18148652/ ) and on the answer https://stackoverflow.com/a/71153467/ provided by the user 'Harun24hr' ( https://stackoverflow.com/u/5514747/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Network days and IF statements

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Counting Business Days in Excel: Including Today's Date for Blank End Dates

In today’s fast-paced work environment, managing time effectively is crucial. One important aspect many of us face in project management or simple scheduling is counting the number of business days between two dates. Business days are usually defined as weekdays (Monday through Friday) that exclude public holidays. However, what do you do when the end date is left blank? In this guide, we'll explore a simple strategy using Excel’s functions to compute business days, while adjusting for any blank cells.

The Challenge

You already have a formula to calculate the number of business days between two dates, namely:

[[See Video to Reveal this Text or Code Snippet]]

Here:

K2 represents the start date.

L2 represents the end date (which poses a problem if it is left blank).

Sheet2!O3:O36 contains the list of public holidays that should be excluded from the count.

The issue arises when L2 is blank, resulting in a bizarre negative number (such as -30000), which doesn't make sense in this context. So, how can we tweak this formula effectively?

The Solution: Using IF with NETWORKDAYS

To resolve this issue, we can incorporate the IF function into our formula. This allows Excel to recognize when L2 is blank and substitute it with today’s date. Here’s how it works:

Updated Formula

Instead of the original formula, use the following:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula

NETWORKDAYS: This function is crucial as it counts the number of working days between two dates, factoring in holidays.

IF(L2="", TODAY(), L2): Here’s where the magic happens:

If L2 is empty (i.e., has no date), the formula will use TODAY() instead.

If L2 has a date, it will use that date for the calculation.

-1: This is subtracted at the end to adjust the count as necessary (optional based on your exact requirements).

Example

K2: 01/01/2023

L2: (blank)

Result: The formula will return the number of business days from January 1, 2023, to today’s date, excluding any public holidays listed in Sheet2.

Wrap Up

Using the updated formula allows you to ensure accurate counts of business days while accommodating for any blank end dates. Whether you're managing project timelines, employee work schedules, or simply planning your personal tasks, having an accurate business day calculation at your fingertips is invaluable.

We hope this post was helpful! Try the formula in your own Excel worksheets and let us know how it works for you. Happy calculating!