NETWORKDAYS.INTL function returning unexpected result

Question

NETWORKDAYS.INTL function returning unexpected result

Claudia Buckley 0

I'm using Excel 2021
=NETWORKDAYS.INTL([@Start2],[@End2],11,$N$3:$N$9) where Sunday is the only weekend day. for start and end days where the cell is blank, it is somehow returning a value of 1

This is a practice file I'm putting together for some learners.
Look at the rows where columns F and G are blank. Column H should be returning a value of 0, but it is returning 1 for some reason

If i use another option in the formula (e.g. 1, 2, or any other number instead of 11), the correct result is returned. Any ideas what is happening here?

0 comments

2 answers

Your answer

Answer 1

Kal-D 7,365 Microsoft External Staff Moderator

Hi Claudia Buckley,

When both the Start and End cells are blank, Excel does not treat them as “no dates.” Instead, blank values are coerced to 0 (a “zero date” in Excel’s 1900 date system, i.e. late December 1899). As a result, NETWORKDAYS.INTL ends up evaluating a single date (start = end) and counts it inclusively.

That’s why the result depends on the weekend pattern you choose:

With weekend code 11 (Sunday only), Saturday is considered a workday. The coerced date falls on a Saturday, so Excel counts it as 1 workday.
With weekend patterns that treat Saturday as a weekend day, that same coerced date is excluded, and the result is 0.

So this behavior is a combination of Excel’s legacy date coercion (blank → 0) and the selected weekend pattern, rather than a bug in NETWORKDAYS.INTL.

If you want blanks to reliably return 0, handle them explicitly:

=IF(OR([@Start2]="",[@End2]=""),
   0,
   NETWORKDAYS.INTL([@Start2],[@End2],11,$N$3:$N$9)
)

This enforces the intended logic of “no dates = no workdays” instead of letting NETWORKDAYS.INTL interpret blank inputs.

Please let me know if this proves useful to you, or if you would like further assistance.

I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Kal-D 7,365 Reputation points Microsoft External Staff Moderator

2026-04-01T23:12:43.3666667+00:00

Hi Claudia Buckley,

I am writing to see how things are going with this issue.

If there’s anything else I can assist you with or if you need further clarification, please don’t hesitate to let me know, I’m here to help.

Looking forward to hearing from you.
Claudia Buckley 0 Reputation points

2026-04-01T23:17:29.8833333+00:00

Wow, that is unfortunate. I guess I can use the workaround, but it isn't great to have to explain to learners that the function works one way sometimes and another way at other times
Kal-D 7,365 Reputation points Microsoft External Staff Moderator

2026-04-02T16:16:13.7066667+00:00

Hi Claudia Buckley,

That reaction makes sense, especially when you’re thinking about how best to explain it to learners.

In this case, Excel is simply applying its standard handling of blank values before the function logic comes into play. Once those blanks are interpreted as a date value, NETWORKDAYS.INTL just evaluates that date using the weekend pattern provided.

When teaching or sharing examples, adding a simple blank check can help keep the behavior aligned with what most people intuitively expect. It allows you to present a clear rule of thumb (“no dates = no workdays”) without needing to go into Excel’s underlying date system unless it’s useful context later on.

It’s not something most users run into day‑to‑day, but the explicit check does make the outcome consistent and easier to reason about.

Answer 2

When both start and end date cells are blank, Excel is not treating them as “no dates”; instead, the arguments being passed to NETWORKDAYS.INTL are being interpreted in a way that still yields one workday when weekend code 11 is used.

From the documented behavior of NETWORKDAYS/NETWORKDAYS.INTL and related implementations:

All arguments must be valid dates; otherwise, NETWORKDAYS / NETWORKDAYS.INTL returns a #VALUE! error, not 0.
For DAX NETWORKDAYS, if either start_date or end_date is BLANK, the BLANK value is treated as DATE(1899,12,30).
For the Excel/VBA NETWORKDAYS.INTL implementation, if the date arguments are out of range, #NUM! is returned; if the weekend string is invalid, #VALUE! is returned.

In the scenario described, the key points are:

The formula does not error, so the arguments are not considered invalid.
With weekend code 11 (Sunday-only weekend), the function returns 1 when both referenced cells are blank.
With other weekend codes (1, 2, etc.), the function returns 0 for the same blank cells.

This pattern matches the kind of behavior seen when a function internally coerces blank values to a default date (similar to DATE(1899,12,30) in the DAX NETWORKDAYS function) and then applies the weekend pattern. Depending on which weekday that coerced date falls on and which weekend code is used, the result can differ by 1 day. In other words, weekend code 11 interacts with that default date in such a way that the function counts one workday, while other weekend codes do not.

Because NETWORKDAYS.INTL requires valid dates and does not define a special “blank means 0 days” behavior, this is expected from the function’s perspective, even though it is unintuitive in a teaching scenario.

To ensure a result of 0 when either start or end date is blank, wrap the function in an explicit blank check, for example:

=IF(OR([@Start2]="",[@End2]=""),0,
   NETWORKDAYS.INTL([@Start2],[@End2],11,$N$3:$N$9)
)

This forces the logic “no dates → 0 days” instead of letting NETWORKDAYS.INTL decide how to coerce blanks.

References:

Share via

NETWORKDAYS.INTL function returning unexpected result

2 answers

Your answer