#  Other Applications & Softwares  > Outlook Formatting & Functions >  > [SOLVED] Distribute value between Start & End Date but exclude a holiday break

## pcronje

Hallo Members

I have managed to create a spreadsheet (as attached) to distribute a construction project task costs between Start & End Dates, in order to generate a cashflow.

My Question to the Forum:
In the southern hemispheres we have a construction break generally between 15 December to 15 January, so I require a method to exclude this Time Period in the cashflow generation. In order words, there should not be any costs displayed during the break-period.

Regards

----------


## Kaper

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:



```
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:


```
Please Login or Register  to view this content.
```



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

```
Please Login or Register  to view this content.
```



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


```
Please Login or Register  to view this content.
```

 

The last part 


```
Please Login or Register  to view this content.
```

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



```
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


```
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 

```
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.

----------


## pcronje

Hi Kaper

Thank you so much for your assistance. This works 110%

Regards
Pierre

----------


## Kaper

Glad to hear about 110%  :Smilie: 

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.

----------

