Verification of date range and provide output

[Pinang]

Hi,

I need a formula or macro to find out output result based on name and date range, if name and date range within the input data then display project else show as no allocation.

Refer below sample data, attached file for more reference.

Input Data:

Name	Start Date	End Date	Project
A	01-Jan-17​	31-Mar-17​	A101
A	01-Feb-17​	31-Dec-17​	B101
A	01-Mar-18​	31-Mar-19​	C101

Output Data:

Name	Start Date	End Date	Output Project Result
A	20-Jan-17​	28-Feb-17​	A101
A	01-Jan-16​	31-Dec-16​	No Allocation
A	01-Mar-17​	30-Sep-17​	B101
A	01-Feb-18​	31-Dec-18​	No Allocation

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[vletm]

Pinang
Are Your expected results correct?
eg cell i3 ... should there also be B101? ... it starts 01-Feb-17.

You could check this files Sheet2's chart (left INPUT with yellow, right Your OUTPUT with black )
... there could be any number of Output Project Result's per row...
I tested to solve this 'my way' ... check Sheet1 ... by press [ Do It ]

 

[bosco_yip]

In cell I3, formula copied down :

=IFERROR(LOOKUP(1,0/(InputNames=F3)/(InputStartDates<=G3)/(InputEndDates>=H3),InputProjects),"No Allocation")

Regards
Bosco

 

[Peter Bartholomew]

I picked up the NARAYAN'S solution to see whether modern dynamic arrays have anything to offer.



The formula I used was
= TEXTJOIN( ", ",1, FILTER( InputProjects, MatchesName? * ContainsPeriod?, "No allocation" ) )

A feature of the formula using TEXTJOIN and FILTER is that it is capable of returning multiple matches.

The Boolean arrays MatchesName? and ContainsPeriod? refer to:
= (InputNames=Name)
= ( InputStartDates <= StartDate ) * ( InputEndDates >= EndDate )
respectively.

 

[Peter Bartholomew]

I have only just noticed that @vletm has already made the point about multiple matches.
If I relax the time criterion to intersecting time intervals, as opposed to one encompassing the other, using IntersectsPeriod?
= ( InputEndDates >= StartDate ) * ( InputStartDates <= EndDate ),
I recover @vletm's results.

 

[vletm]

Peter Bartholomew
... could You check eg Your from 01-Jan-17 to 31-Dec-19 given result?
For my eyes, all three blacks-F overlaps with yellow-F . (There could also be some other differences ... with 'mine')
Below chart should show with yellow INPUTs and with black OUTPUTs

 

[Peter Bartholomew]

@vletm
Nice chart.
I have just realised that I barely check the results of a calculation; my attention is focussed on "is the formula correct?" and from there I assume that a correct formula will generate correct results. In this case I believe the results in #6 tie up with yours in #3.
Results of #5 and #4 also correspond. Name F looks correct to me.

 

[vletm]

Peter Bartholomew
Your my attention is focussed on "is the formula correct?
Yes, result would verify it. =A+B ... =A*B, both are correct, but how about result?

Name F ... name looks correct, but how about 'overlaps'?

Copy from Your #5 ...
Left side F >> L101 | 01-Jan-17 to 31-Dec-18
Right side F >> No . | 01-Jan-17 to 31-Dec-19 ... whole L101

.. one other hmm?
Left side A >> A101 | 01-Jan-17 to 31-Mar-17
Left side A >> B101 | 01-Feb-17 to 31-Dec-17
Right side A >> A101 | 20-Jan-17 to 28-Feb-17

 

[Peter Bartholomew]

Hi @vletm
I agree that, to be a correct formula, more is needed than simply being a valid formula. It should also capture the requirement and be consistent with any relevant domain knowledge. That is why I never use direct cell referencing, which I believe obscures the link between the proposed solution and the problem. Even then, I can make a total mess of a solution (I seem to be more prone to error than most )!

In the context of results for name F:
One of my formulae is meant to exclude any output range that does not fall entirely within the corresponding input range. Thus the first row returns "No allocation" because the period from 1-Jan-19 to 31-Dec-19 falls outside the range specified by the input table. The other formula only excludes an output if there is no overlap, so produces more matches.

I admit to not have a complete understanding of the requirement, hence the different proposed solutions.
It is also a feature of my non-standard development practices that I have a single point at which I can change the named formula ContainsPeriod? into IntersectsPeriod? and the effect of the redefinition propagates throughout the solution without the need to change the individual formulas held in multiple cells (a task that involves inherent risk).