Date Calculation Issue

[chirayu]

Hi Guys,

I've attached a sample file for you to look at. The issue is explained within. Have been unable to work out a formula or vba solution for this. Its a date based calculation. Help is appreciated. Thanks in advance.

 

[eibi]

It looks like your date formats on the target sheet may be inconsistent...sometimes mm.dd.yyyy and other times dd.mm.yyyy

Before trying to troubleshoot the vba, can you confirm that the dates are formatted consistently?

(original file attached)

 

[Chihiro]

Let me see if I understood it right...
Issue is when start date of the range fall in middle of instance?

Formula wise, can you use =SUM(IF(FREQUENCY(),1,0)) array formula?

Something like below:
=SUM(IF(FREQUENCY(IF(($I$2:$I$368<>"")*($H$2:$H$368>=Start Date)*($H$2:$H$368<=End Date), ROW($I$2:$I$368)),IF(($I$2:$I$368="")*($H$2:$H$368>=Start Date)*($H$2:$H$368<=End Date),ROW($I$2:$I$368))),1,0))

Confirmed as array (CSE) and replace Start Date & End Date with calculation or reference.

 

[vletm]

@chirayu
You have Your sick holidays in A2:B7
You can solve You 'Instances' with those dates, not with J-column.
If Your one year periods dates (31-Oct-15 ... 31-Oct-16) and
any sick holiday (01-Jan-16 ... 06-Jan-16 and so on) hit Your 'one year' then it increase 'Instances'-value.
I tried to make idea of 'yellow Instances section for You'.
Hmm...

 

[chirayu]

It looks like your date formats on the target sheet may be inconsistent...sometimes mm.dd.yyyy and other times dd.mm.yyyy

Before trying to troubleshoot the vba, can you confirm that the dates are formatted consistently?

(original file attached)

@eibi yeah had attached the wrong file - correct file is the v2 that I reattached

 

[chirayu]

Thanks @vletm @Chihiro both solutions work well

Chihiro your solution is quite difficult to understand. Not sure how its working

 

[Chihiro]

I'm terrible at explaining formulas but here's my attempt.

First lets simplify formula be using smaller range.

Taking your sheet's set up (added 1/8 & 1/9 with sick days for demonstration).



And start and end is 1/5/2016 and 1/9/2016 respectively.
Formula to calculate instances, becomes...

=SUM(IF(FREQUENCY(IF((I2:I13<>"")*(H2:H13>=H6)*(H2:H13<=H10), ROW(I2:I13)),IF((I2:I13="")*(H2:H13>=H6)*(H$2:H13<=H10),ROW(I$2:I$13))),1,0))

Lets look as component parts.
Inner IF: IF((Range<>"")*(DateRange>=StartDate)*(DateRange<=EndDate),ROW(RANGE))

Which is Data Array for Frequency.

This simply checks for rows within range that matches each criteria and returns array.
Range<>"" = {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}
or
{1;1;1;1;1;1;0;1;1;0;0;0}

DateRange>=StartDate =
{0;0;0;0;1;1;1;1;1;1;1;1}

So combining the 2 it becomes (1 where both are true, 0 otherwise).
{0;0;0;0;1;1;0;1;1;0;0;0}

DateRange<=EndDate =
{1;1;1;1;1;1;1;1;1;0;0;0}

Combine and:
{0;0;0;0;1;1;0;1;1;0;0;0}

ROW(Range) =
{2;3;4;5;6;7;8;9;10;11;12;13}

Combine Row Range with TRUE/FALSE array:
{FALSE;FALSE;FALSE;FALSE;6;7;FALSE;9;10;FALSE;FALSE;FALSE}

Bin Array for Frequency is second IF in same format as Data array, only difference being checking for where Range="".

It evaluates to...
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;FALSE;FALSE;FALSE;FALSE;FALSE}

FREQUENCY then evaluates to {2;2}. 2 sets of consecutive occurrences.

SUM(IF({2;2},1,0)) portion simply adds 1x # of times conditions are met.
=1 + 1 = 2