Formula Help - Remaining Hours To Be Worked In A Term, Equally Spaced Out Monthly

Nicole J

All,

You've always been good to me. I'm hoping to get across the finish line for a formula I've been working on.

Here's what I have:

* Start Date, End Date, Working Days within that range (could be one week, could be ten years)

* Remaining Hours (total hours necessary to be worked within the Start & End Date)

I need a formula that uses the above fields to say "if X hours remain to be worked within the range, how many hours would that work out to for May 2023, June 2023, July 2023 (through the end of the term).

Many thanks.

Nicole J

Never mind. I've got it.

Thanks, Paul.

Paul Newcome

Are you able to provide some screenshots for context?

Nicole J

Paul Newcome

You are going to need something along the lines of the below:

https://community.smartsheet.com/discussion/103609/formula-for-amount-spread-across-months

Nicole J

Thanks, Paul.

However, I'm not quite able to get it right.

For May 2023, shouldn't I be able to have this work:

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 5, 1), DATE(2023, 5, 1), IF([Start Date]@row > DATE(2023, 5, 31), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 5, 1), "", IF([End Date]@row < DATE(2023, 5, 31), [End Date]@row, DATE(2023, 5, 31)))) * [Remaining Hours]@row / [Working Days Remaining In Term (Servicing)]@row, "-")

I'm sure I'm missing something simple, but this is frying my brain.

See attached (trying to solve for the Yellow Highlight (and then tweaking the formula for each month of the whole term - unique columns out to the right

Monthly Hours To Be Worked.docx

).

Paul Newcome

Are you getting an error?

Nicole J

So when I go for this:

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 5, 1), DATE(2023, 5, 1), IF([Start Date]@row > DATE(2023, 5, 31), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 5, 1), "", IF([End Date]@row < DATE(2023, 5, 31), [End Date]@row, DATE(2023, 5, 31)))) * [Remaining Hours]@row / [Working Days Remaining In Term (Servicing)]@row, "-")

It works great.

However, if my "Start Date" and "End Date" spans more than a year, then my "Working Days Remaining In Term (Servicing)" formula becomes "#INNVALID DATA". So, perhaps that's my issue.

The formula I'm using for "Working Days Remaining In Term (Servicing)" is:

=IF(TODAY() < [Start Date]@row, NETWORKDAYS([Start Date]@row, [End Date]@row), NETWORKDAYS(TODAY(), [End Date]@row))

By the way, THANKS.

Nicole J

Never mind. I've got it.

Thanks, Paul.

Paul Newcome

Glad you were able to get it sorted.