Distribute value between Start & End Date but exclude a holiday break

I don't think nested ifs is the effective way to deal with tha. Note that even with no-holiday period (task 1) the calculations dont sum to total cost (column F).

My proposition is to use the list of all days in a holiday period and use networkdays function. So having a list Dec 12 till Jan 15 in AV1:AV35 a formula in H7 (and copy down and right) could read:

Formula:

Please Login or Register  to view this content.

May look as long one but it consists of checking if a beginning of a week is in holiday period:

Formula:

Please Login or Register  to view this content.

(could be done also by simple testing two limiting dates, like

Formula:

Please Login or Register  to view this content.

So if it is 0, we have result 0 and if not the main part:

Formula:

Please Login or Register  to view this content.

The last part

Formula:

Please Login or Register  to view this content.

is (I hope) obvious. It's our daily spending (CostToComplete/NumberOfWorkingDays)

Formula:

Please Login or Register  to view this content.

it is the last day of given week (start of week+5) or last day of work (we had to add 1 here, because otherwise task starting and ending the same day would be 0 day long), whichever of these two days is earlier
and we take also a later date of the two (start date and beginning of given week

Formula:

Please Login or Register  to view this content.

the difference between the two is the number of days worked in given week. But note that before startday or after endday we will have wrong results, namely: negative numbers. So we take only positive values using

Formula:

Please Login or Register  to view this content.

Check it out but first check your dates in E9 you have Jan 23rd 5PM. It would lead to wrong result in X9 (last week). The same mistake is in E10, but as it's the date during weekend makes no effect on results.

PS. As you are using excel 365 the date range containing holidays could be dynamically created but I think it would make a formula even harder to read.

Glad to hear about 110%

So as that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.