Count distinct days from two groups of days

[Colin]

Hello,

Can anybody help please?

I have used Networkdays() to count the working days between two dates. And
have had to do this twice. So now have two counts that I will add together.

But if the same particular date is in both count sets I dont want to count
it twice?

Hope this makes sense,

Thanks in advance

 

[Ron Rosenfeld]

Hello,

Can anybody help please?

I have used Networkdays() to count the working days between two dates. And
have had to do this twice. So now have two counts that I will add together.

But if the same particular date is in both count sets I dont want to count
it twice?

Hope this makes sense,

Thanks in advance

It depends on how your data is set up but

If you are using NETWORKDAYS on two sets of data and
there are particular dates in both sets, then
it must be the case that the date ranges overlap.

If the date ranges overlap, and if the holiday dates are the same for both
ranges, then you could just use NETWORKDAYS using the earliest start date and
the latest end date.

And you could test to see if there is an overlap.

So maybe something like:

=IF(StartDate2<=EndDate1,NETWORKDAYS(StartDate1,EndDate2),
NETWORKDAYS(StartDate1,EndDate1)+NETWORKDAYS(StartDate2,EndDate2))

--ron

 

[Jacob Skaria]

Just to add on to what Ron has posted...

If the date series are not in sequential order; try
A1 = StartDate1
B1 = EndDate1

A2 = StartDate2
B2 = EndDate2

=IF(MEDIAN(A1,B1,A2)=A2,NETWORKDAYS(MIN(A1,A2),
MAX(B1,B2)),NETWORKDAYS(A1,B1)+NETWORKDAYS(A2,B2))

 

[Colin]

Thank you all very much for the quick and useful responses.
The two date ranges are sequential inside their own sets, but one set could
come wholly before, during, overlapping or after the second.

Would the best way be to use the Median() check as advised by Jacob but
check for A1 being the middle date also?