Incorrect network days

UrbanNomad

Hi All,

I'm trying to replicate the same formula in smartsheet, the issue is network days are calculated incorrectly, is my formula wrong?

I have attached the image:

from Jun 3 - Jun 19 there are 13 network days, while splitting for the week beginning Jun 15, it only shows 3 workdays instead of 5.

any advise/suggestions?

Genevieve P.

Hi @UrbanNomad

Could you post an image of how you're using this in Smartsheet? What Smartsheet formula are you using?

The NETWORKDAYS function can return the number of working days between two dates, however you would need to specify what those dates are. In Smartsheet, Dates are recognized in date columns, so your 9th row in the image above would be seen as text and not a date.

UrbanNomad

Hi @Genevieve P

Attaching the image of smartsheet formula

In number columns I have entered the formula, to compare against the date columns in red , if you notice for first row between Jun 8 - Jun 24 there are 13 network days, as per my formula it only allocated 11 hours, instead of 13.

Hope this helps

Stefan

Hi,

without analyzing your setup and formulas I have 2 ideas.

1) Has your account calendar additional public holidays in this period?

2) I see, that you are missing 2 days/hours. Is it possible, that your formula does not count the first and the last day of the period?

Hope this helps

Stefan

UrbanNomad

@Stefan

1) No holidays included

2) Yes, it is missing 2 days/hours, that's where I'm stuck and trying to figure out what part of my formula needs correction.

Thanks

Urban

Genevieve P.

Hi @UrbanNomad

It's taken me a while to understand your formula, so I'm going to break it down below in pieces as this has helped me:

Full Formula:

=MAX(NETWORKDAYS([Date 1]$1, [Date 1]$1 + 6)) - MAX(START1 - [Date 1]$1, 0) - MAX([Date 1]1 + 6 - FINISH1, 0)

First piece:

=MAX(NETWORKDAYS([Date 1]$1, [Date 1]$1 + 6))

The NETWORKDAYS function is counting the days between one date and 6 days later, which will always return 5, as there are 5 working days in a week.

Therefore, your formula can be represented in this way:

=MAX(5 - MAX(START1 - [Date 1]$1, 0) - MAX([Date 1]1 + 6 - FINISH1, 0)

Second piece:

MAX(START1 - [Date 1]$1, 0)

This checks the Start date minus the Date in the first Date column, and either returns the number of days between them or 0.

Third Piece:

MAX([Date 1]1 + 6 - FINISH1, 0)

This checks 6 days after the date in the first Date column and minuses the FINISH date off of it. Therefore, if the finish date is in the past, it will return 0. If it's in the future, you will have a number representing the days between those dates.

So now, if we take that third column to see why it's outputting 1 instead of 3, lets look at the Math:

=MAX(5 - MAX(START1 - [Date 3]$1, 0) - MAX([Date 3]1 + 6 - FINISH1, 0)

or

=MAX(5 - MAX(06/08/20 - 06/22/20, 0) - MAX(06/22/20 + 6 - 06/24/20, 0)

or

=MAX(5 - MAX(-14, 0) - MAX(06/28/20 - 06/24/20, 0)

or

=MAX(5 - MAX(-14, 0) - MAX(4, 0)

or

=MAX(5 - 0 - 4) = 1

And since 1 is greater than 0, the overall MAX will show 1 instead of 0. From here, I'm not sure how to adjust this to say 3 though. Does this make sense?

UrbanNomad

@Genevieve P Yes, makes sense, it's picking any value greater than 0, instead of actual number, would adding a "IF" help?