How do I return the number of workdays in a month using only the start date?

Dave Bowie

Andrée Starå

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!

Kelly Moore

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

Jason Duryea

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.

https://help.smartsheet.com/function/networkdays

https://help.smartsheet.com/function/networkday

Andrée Starå

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!

Dave Bowie

That worked great thank you!

Andrée Starå

@Dave Bowie

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)

Rodney

Hello Andrée,

It works except for December, is there a reason or correction for this?

Allison Horn

I'm having the same issue with it not working for December, any correction to the formula?

Kelly Moore

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

Allison Horn

That, unfortunately, did not work.

Genevieve P.

Hi @Allison Horn

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

Kelly Moore

oops, good catch, Genevieve. My bad.

Allison Horn

Awesome, that worked! Thank you

Dave Bowie

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

Dave Bowie

@Genevieve P.

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

Kelly Moore

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?

Dave Bowie

Start date is Dec. 1st

#unparseable