Networkdays formula for a year and a month
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.
Best Answer

This answers the question. Thanks Paul.
Answers

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.

@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).

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

@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.

This answers the question. Thanks Paul.

Haha. That "S" can really make a difference.
Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!