Networkdays formula for a year and a month

Vivien Chong

I am trying to work on the above for my Sales Team.

Let's say the target sales is $1,000,000 for the year, and they have achieved $104,000.

For the remaining target of $896,000, I would like to show the remaining target for this month by dividing $896,000 with remaining working day this year, multiply by remaining working day this month.

Which means "This month remaining target" will fluctuate depending on the sales performance. If it is not achieved, then next month will have higher target to achieve.

Appreciate any help on this.

Thank you.

Vivien Chong

https://community.smartsheet.com/discussion/comment/316934#Comment_316934

This answers the question. Thanks Paul.

Paul Newcome

Ok. So the first trick is to get the number of working days remaining in the current month. To do that we need to be able to generate the last day of the current month.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Now we can drop this in a NETWORKDAYS function alongside the TODAY function to figure out how many are left.

1.

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

The remaining working days in the current year is rather simple because the last day of the year is always Dec. 31.

2.

=NETWORKDAYS(TODAY(), DATE(YEAR(TODAY()), 12, 31))

And the final piece is Target - Achieved

3.

=Target@row - Achieved@row

Now we just need to piece them all together. We want the remaining $ (3) divided by the number of days left in the year (2) and then multiplied by the number of days left in the month (1).

(3 / 2) * 1

=((Target@row - Achieved@row) / NETWORKDAYS(TODAY(), DATE(YEAR(TODAY()), 12, 31))) * NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

And that should do the trick for you.

Vivien Chong

@Paul Newcome Thanks for your prompt assistance.

I tried on the formula and I got the following:

With formula (2)

With formula (1), January's remaining days are 6 as of today 24/1/2022.

I use the Networkdays(Start Date, End Date) formula to manually count the monthly workdays and the total is 247 days, vs the calculation from formula 2 that reads 245 days.

I wonder why is there an additional / missing 2 days (245 vs 247).

Paul Newcome

When I do NETWORKDAYS from 24 Jan 2022 to 31 Dec 2022, I get 245.

Vivien Chong

@Paul Newcome I found the cause. :D. I use NETWORKDAY. When I changed it to NETWORKDAYS, it turns out fine.

How silly I am counting the NET Working Days for each month where I can count it in just one formula. :D

Thanks for your quick help.

Vivien Chong

https://community.smartsheet.com/discussion/comment/316934#Comment_316934

This answers the question. Thanks Paul.

Paul Newcome

Haha. That "S" can really make a difference.

Happy to help. 👍️