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

Options
✭✭✭✭

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.

Tags:

• ✭✭✭✭
Options

Never mind. I've got it.

Thanks, Paul.

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for context?

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
edited 05/03/23
Options

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

• ✭✭✭✭
edited 05/04/23
Options

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

).

• ✭✭✭✭✭✭
Options

Are you getting an error?

• ✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Never mind. I've got it.

Thanks, Paul.

• ✭✭✭✭✭✭
Options

Glad you were able to get it sorted.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!