This Month's/Year's Net Work Days As Of TODAY

Nicole J

So, August 2024 has 22 Net Work Days. But as of today (08/07/2024), there are only 18 Net Work Days remaining in August.

Is there a formula I can create that will show me "X Month's/X Year's" remaining work days?

So the August 2024 answer would be 18 as of today, September 2024 would be all 20 remaining work days, October 2024 would be all remaining 23 work days, etc..

Thoughts?

Okay, I'm onto something. I can calculate the remaining net work days in any "current" month by using this formula:

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

But how can I tweak this formula to look as specific month/year combos?

Paul Newcome

You could actually use a single IF with an OR statement to say that if one of them is true then output the "-", otherwise run calc, Or you can do the reverse logic with an AND statement and say that if all of them are not true then output the calc, otherwise output the "-".

First one would probably be easier to write based on your outline of the requirements.

=IF(OR(Deadline@row < DATE(2024, 09, 01), Deadline@row < TODAY(), Deadline@row = "", [Remaining Hours]@row < 0), "-", original_calc)

Jason Tarpinian

Using "Date Reference" as the variable that can be any date/month/year. You have to be careful with your formula, as December will give you some errors, as MONTH(TODAY()) + 1 would be 13, giving you #INVALID COLUMN VALUE error.

Workdays remaining in a month:

=NETWORKDAYS([Date Reference]@row, DATE(IF(MONTH([Date Reference]@row) = 12, YEAR([Date Reference]@row) + 1, YEAR([Date Reference]@row)), IF(MONTH([Date Reference]@row) = 12, 1, MONTH([Date Reference]@row) + 1), 1) - 1)

Workdays remaining in a year:

=NETWORKDAYS([Date Reference]@row, DATE(YEAR([Date Reference]@row), 12, 31))

Nicole J

Thanks, Jason. I wasn't able to get your formula to work (kept getting the #UNPARSEABLE error), but I was able to figure out this formula for August 2024.

=IF(AND(YEAR(TODAY()) = 2024, MONTH(TODAY()) = 8), NETWORKDAYS(TODAY(), DATE(2024, 9, 1) - 1), NETWORKDAYS(DATE(2024, 8, 1), DATE(2024, 8, 31)))

Then for September 2024 and beyond I just changed the month and year numbers in the formula.

Appears to be working.

Paul Newcome

Try something like this:

=MAX(NETWORKDAYS(MAX(TODAY(), DATE(2024, 10, 1)), DATE(2024, 11, 1) - 1), 0)

The above is for October 2024. To adjust for other months, the first DATE is the first of the month you want, and the second DATE is the first of the month after. There are ways to modify the above as well if you would rather use a date type column as a reference.

Nicole J

Thanks, Paul. Works like a charm. I've got another one for you, if you'd be so kind as to oblige.

I've got the following formula and it works great.

=[Remaining Hours]@row / [Net Working Days Between "Today" and "Deadline"]@row * [09/2024 Net Working Days Remaining]@row

However, I need to add in the following IF situations:

• IF the "Deadline" cell@row is prior to 09/2024, then display an "-"
• IF the "Deadline" cell@row is in the past (regardless of month/year), then display an "-"
• IF the "Deadline" cell@row is blank, then display an "-"
• IF the "Remaining Hours" cell@row is a negative value, then display an "-"

I know it's a lot of IFs, and that's what's tripping me up when it comes to devising the formula.

Any help would be appreciated.

Paul Newcome

You could actually use a single IF with an OR statement to say that if one of them is true then output the "-", otherwise run calc, Or you can do the reverse logic with an AND statement and say that if all of them are not true then output the calc, otherwise output the "-".

First one would probably be easier to write based on your outline of the requirements.

=IF(OR(Deadline@row < DATE(2024, 09, 01), Deadline@row < TODAY(), Deadline@row = "", [Remaining Hours]@row < 0), "-", original_calc)

Nicole J

Paul, you're a wizard. THANK YOU. This works like a dream. It would've taken me days to get to this efficiency.

Paul Newcome

Happy to help. 👍️