Formula Help Within Sheet

Try this:

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 2, 1), "", IF([End Date]@row < DATE(2023, 2, 28), [End Date]@row, DATE(2023, 2, 28)))) * [Allocation %]@row * 8, "No Feb 2023 Work Days")

All comments

Paul Newcome

Will your start and end dates always be Feb - Mar? Are you only calculating for Feb - Mar, or does this expand to more month columns? If there are more month columns, do you need to be able to account for going into another year?

Thanks, Paul. No, the February through April dates I gave in my example are just an example. The real life sheet will have dates spanning all months and into future years (there are columns for "February 2023, March 2023, April 2023, May 2023, through many years". So, perhaps the formula will have to designate both Month and Year. I hope this helps. Thanks for looking into this.

Paul Newcome

Ok. There are a number of other posts here in the Community that have the base already built out where we spread the number of days across multiple columns. I remember helping out on a few, so we just need to find one and then tweak it. If you happen to find one of those posts before I do, feel free to drop a link here and we can work it from there.

Hello, Paul (or anyone else),

I'm writing to follow-up on this thread. I've searched through the history of similar issues, and I found a thread from April 2022 (thread titled "Formula for Hours Scheduled By Month") and came across the below formula. When applied, it's not quite there yet (giving me error messages). I'm hoping this maybe jogs your memory and you can help modify the below formula to my issues. Thanks.

=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours", IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " hours", IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row > IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1), NETWORKDAYS(DATE(2022, 03, 01), IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours")))

Does this work?

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), DATE(2023, 2, 28)), "No Feb 2023 Work Days") * [Allocation %]@row * 8

I forgot I changed something which broke the IFERROR. This is what I was going for:

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), DATE(2023, 2, 28)) * [Allocation %]@row * 8, "No Feb 2023 Work Days")

Thanks, Mike. Right now, that formula works when placed in a row that has February dates. But when placed in a cell without February dates falling between the "Start Date" and "End Date", I'm getting the same total (where hopefully, I'd get the "No Feb 2023 Work Days" answer).

When Start Date is 01/30/2023 and End Date is 03/31/2023 and Allocation % is 70%, I'm getting the correct 112 hours for February.

But, on a different row, the Start Date is 09/26/2022 and End Date is 01/27/2023 (therefore zero February working days) and Allocation % is 70%, I'm still getting the 112 hours for February, but I think I should be getting "No Feb 2023 Work Days".

You're welcome. It looked to work for me when I changed dates around. Now that I think of it though, I didn't add something to account for the End Date if it were say 02/23/2023 or something. That could be added in to the formula though without too much effort.

I believe this might account for that solution:

=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 2, 28), [End Date]@row, DATE(2023, 2, 28))) * [Allocation %]@row * 8, "No Feb 2023 Work Days")

Mike, we're super close. Thank you. I think it's not accounting for start and end dates that are outside of February 2023 (or perhaps even further in the past). For instance, I'm getting an answer of -22.4 when the start and end dates are 09/26/2022 and 01/27/2023.

Try this: