Calculate unique days in multiple overlapping date ranges( not include Sundays-holidays)

[aargiros]

Hello all,

i have attached an example excel that I have a sheet in which one column contains a start date and another one an end date. Also I have a third column in which i want to calculate the unique days of my records(for example 23 days). In this determination i don't want to count Sundays and the holidays.

For example:

Date format (DD/MM/YYYY)

01/03/2020 - 05/03/2020 Holidays date: 02/03/2020 & 25/03/2020
14/03/2020 - 26/03/2020
08/03/2020 - 16/03/2020
Days:17


I hope to undertstand my problem and thanks in advance for your help

[Kaper]

If you have your holidays dates listed in H1 and H2 you could use just:

Please Login or Register  to view this content.

[XLent]

The below would work, I think, but it's a bit brute force and tbh you would most likely be best served using a User Defined Function.

The use of a couple of dynamic named ranges is really just to simplify should you add/remove dates (no need to modify range references)

Please Login or Register  to view this content.

with the sample file as provided the above would return same as Kaper's post but your example in the initial post implies you need to account for possibility of not every date being with the range(s) - so int he attached I added some additional date ranges which don't overlap completely - so you can see the above will adjust/account for that.
(there's no doubt a simpler formula-based approach that is escaping me... )

[aargiros]

Hello Kaper,
Thanks for your answer but it is not the solution that i need as i want only one formula that will return the unique days( no Sundays, Holidays) of all these periods
Lets explain you with more details
I have a product that has promo price for these periods (startDate-EndDate) in differnt stores. At the end of month for example i want to see a grand total of the days that i sell the product with promo.

In this forum i find a formula =SUM(--(MMULT((ROW(INDIRECT(MIN($A$1:$B$4)&":"&MAX($A$1:$B$4)))>=TRANSPOSE($A$1:$A$4))*(ROW(INDIRECT(MIN($A$1:$B$4)&":"&MAX($A$1:$B$4)))<=TRANSPOSE($B$1:$B$4)),{1;1;1;1})>0)), but count Sundays and holidays and for my case i dont want

I hope to have a clear view for my problem now and can help me

[XLent]

Apologies, I missed the fact you were excluding only Sundays (and Public Holidays).

Revised version attached which, per Kaper, makes use of NETWORKDAYS.INTL to account for above.

the formula in the attached is along the lines of the above, but is extended to be dynamic, non-volatile and leverages the NETWORKDAYS.INTL functionality.

[Kaper]

Have a look on the solution by XLent above.
He obviously understood you better.
As for his comment - I'd also go for User Defined function in such case, because formula is rather complicated .

[aargiros]

The below would work, I think, but it's a bit brute force and tbh you would most likely be best served using a User Defined Function.

The use of a couple of dynamic named ranges is really just to simplify should you add/remove dates (no need to modify range references)

Please Login or Register  to view this content.

with the sample file as provided the above would return same as Kaper's post but your example in the initial post implies you need to account for possibility of not every date being with the range(s) - so int he attached I added some additional date ranges which don't overlap completely - so you can see the above will adjust/account for that.
(there's no doubt a simpler formula-based approach that is escaping me... )

Hello XLnet,

First of all thank you for your help. Your suggestion about the gap period is correct.
I think that excel works proparly but i will test it tommorow with some examples.
You suggest me to use User Defined Function. I found the code to determine the days but it does not exclude Sundays and Holiday
Is it easy to you to create and write this specific UDF?

Thanks a lot

[Kaper]

One of possible implementations:

Please Login or Register  to view this content.

and usage in the worksheet:

Please Login or Register  to view this content.

See attachment

[XLent]

edit: Kaper's approach is the more elegant.

Not really tested but one option might be:

Please Login or Register  to view this content.

called from a cell, with your Sun only condition, per below:

Please Login or Register  to view this content.

this would return 39, in sync with the file attached in my prior post (with the additional April date ranges, for testing gaps)

[Bo_Ry]

I got 24

Please Login or Register  to view this content.

Create DateList and Holiday Table
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.

[aargiros]

Good Morning to all.

I believe that i have many solutions so i wiill test them to my data in order in order to decide which one to choose.
I thank each of you individually for your prompt and helpful help

[aargiros]

One of possible implementations:

Please Login or Register  to view this content.

and usage in the worksheet:

Please Login or Register  to view this content.

See attachment

Hello Kaper,

I used your formula and I noticed that when in my records with the date, there is a blank line, it counts as an extra day. For example suppose that I have 5 blank lines at the end of the calculation will add one day.
One solution is to count: uwd(J352:K386;N$2:N3) -1
Is there any other solution? For example to add code to UDF in order to not count the blank line?

26/3/2020 31/3/2020
30/3/2020 31/3/2020

16/3/2020 22/3/2020
23/3/2020 29/3/2020

1/3/2020 9/3/2020
20 (the correct answer is 19)


Thanks in advance

[XLent]

On Kaper's behalf (as not online)

Please Login or Register  to view this content.

[Kaper]

Thnx XLent

@aargiros: let me mention that XLent's code could be ammended in similar manner.

Please Login or Register  to view this content.

I've not checked PQ solution by Bo_Ry, but probably it also shall be changed to properly deal with Null values (empty cells).

[aargiros]

unfortunately I cant use the solution of Bo_Ry as my excel version is incompatible with this query.

I choose Kaper solution with the following code:

Function UWD(dates As Range, holidays As Range) As Long
Dim mydays As New Collection
Dim i As Long, oneday As Date, arr As Variant
arr = dates.Value
On Error Resume Next
For i = LBound(arr) To UBound(arr)
For oneday = arr(i, 1) To arr(i, 2)
If WorksheetFunction.NetworkDays_Intl(oneday, oneday, 11, holidays) Then mydays.Add 1, CStr(oneday)
Next oneday
Next i
On Error GoTo 0
UWD = mydays.Count
End Function

In which point of above code I will add the procedure "for...to... " for the null values(empty cell) ?

[Kaper]

Here you are:

Please Login or Register  to view this content.

Please: take a moment to edit your post and add the CODE tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here