How do I return the number of workdays in a month using only the start date?
Best Answers
-
Hi @Dave Bowie
I hope you're well and safe!
Try something like this.
=NETWORKDAYS(Start@row, DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1) - 1)
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey Dave
In your IF formula, you referenced 'start day' but it doesn't have the square brackets required for a column name containing a space.
This should work for you
=IF(MONTH([start day]@row) = 12, NETWORKDAYS([start day]@row, DATE(YEAR([start day]@row), 12, 31)), NETWORKDAYS([start day]@row, DATE(YEAR([start day]@row), MONTH([start day]@row) + 1, 1) - 1))
Kelly
Answers
-
Given that every month varies what day of the week it starts and the total number of days, I don't think this can be calculated without both the Start and End Dates.
The NETWORKDAY() or NETWORKDAYS() functions are probably the easiest ways to calculate this, but both require a start and end date.
-
Hi @Dave Bowie
I hope you're well and safe!
Try something like this.
=NETWORKDAYS(Start@row, DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1) - 1)
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
That worked great thank you!
-
Excellent!
Happy to help!
I also answered your other comment, which Paul also answered, and then I noticed that I missed two parts that Paul included. (see below and edit as needed)
=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row), 1, 1)) - 1)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
I'm having the same issue with it not working for December, any correction to the formula?
-
Hey @Rodney and @Allison Horn
Try this. The issue with December is the next month number (January) drops to the number 1 and the Year value changes. The formula could be built to look for Jan 1 however it is simpler, for this one month, to specifically look for the end of December.
=IF(MONTH(Start@row) = 12, NETWORKDAYS(Start@row, DATE(YEAR(Start@row), 12, 31), NETWORKDAYS(Start@row, DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1) - 1))
Will this work for you?
Kelly
-
That, unfortunately, did not work.
-
It looks like you might be missing a parentheses.
You'll want to only have two )) at the very end of the formula, and two again )) after the DATE( function but before the second NETWORKDAYS:
NETWORKDAYS(..., DATE(YEAR(..), 12, 31)), NETWORKDAYS(...
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
oops, good catch, Genevieve. My bad.
-
Awesome, that worked! Thank you
-
this isn't working for me, how can I fix it?
=IF(MONTH([start date]@row) = 12, NETWORKDAYS([start date]@row, DATE(YEAR([start date]@row), 12, 31)), NETWORKDAYS([start date]@row, DATE(YEAR([start date]@row), MONTH([start date]@row) + 1, 1) - 1))
-
this isn't working for me, how can I fix it?
=IF(MONTH([start date]@row) = 12, NETWORKDAYS([start date]@row, DATE(YEAR([start date]@row), 12, 31)), NETWORKDAYS([start date]@row, DATE(YEAR([start date]@row), MONTH([start date]@row) + 1, 1) - 1))
Thanks Dave
-
Hey Dave,
When you say it isn't working for you, what is happening? Are you receiving an error (what error?), an incorrect result? Can you give an example of your start date?
-
Start date is Dec. 1st
#unparseable
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!