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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!