Date Range IF, Then Else

[charlies_stuck]

Hi All - Not sure what to search on for this, and so posted it ....thanks for any help in advance.

Im not very advanced in xls...

I would like a formula that looks up a date and returns a value or joined value in a new cell.

So

Sheet 2 is

and Sheet 1

The rules in simple terms
Check Start date in Sheet1 (B2:B5) against the list in Sheet2 Col D (D2:D16)
and
where the Start date in Sheet 1 is equal or greater than Sheet2 Col D (D2:D16) but less than Sheet2 End Date (E2:E16)
Then
Populate Sheet2 Col D with the corresponding Sheet2 Sprint and Sheet2 Col E with the coresponding Week
And if no match then "" (blank)


I need this as I have a long list of tasks in a project that can have date changes and i would rather have it done automatically.

Also...bonus karma is you know how to do this as a MS Project Formula

Thanks

C

PS hope i made sense.

 

[charlies_stuck]

Hi All - Not sure what to search on for this, and so posted it ....thanks for any help in advance.

Im not very advanced in xls...

I would like a formula that looks up a date and returns a value or joined value in a new cell.

So

Sheet 2 is

View attachment 75457

and Sheet 1

View attachment 75458

The rules in simple terms
Check Start date in Sheet1 (B2:B5) against the list in Sheet2 Col D (D2:D16)
and
where the Start date in Sheet 1 is equal or greater than Sheet2 Col D (D2:D16) but less than Sheet2 End Date (E2:E16)
Then
Populate Sheet2 Col D with the corresponding Sheet2 Sprint and Sheet2 Col E with the coresponding Week
And if no match then "" (blank)


I need this as I have a long list of tasks in a project that can have date changes and i would rather have it done automatically.

Also...bonus karma is you know how to do this as a MS Project Formula

Thanks

C

PS hope i made sense.

Sorry Cant see how to edit.

Think I got it wrong ..dooh

Check Start date in Sheet2 (B2:B5) against the list in Sheet1 Col D (D2:D16)
and
where the Start date in Sheet2 is equal or greater than Sheet1 Col D (D2:D16) but less than Sheet1 End Date (E2:E16)
Then
Populate Sheet2 Col D with the corresponding Sheet1 Sprint and Sheet2 Col E with the corresponding Week from Sheet1 Col C
And if no match then "" (blank)

 

[Alex Blakenburg]

See if this works for you.

20221005 Lookup Filter charlies_stuck.xlsm
	A	B	C	D	E
1	Task	Start	Finish	Sprint	Week
2	Lunch	13/06/2022	12/07/2022	Sprint 6	Week 3
3	Dinner	11/07/2022	17/08/2022	Sprint 7	Week 2
4	Supper	5/09/2022	24/12/2022
5	Drink	3/10/2022	21/01/2023
Sheet1

Cell Formulas
Range	Formula
D4:D5,D2:E3	D2	=FILTER(Sheet2!$A$2:$B$11,(Sheet2!$C$2:$C$11<=$B2)*(Sheet2!$D$2:$D$11>=$B2),"")
Dynamic array formulas.

And in case anyone else want to try, this is my sheet 2 as an XL2BB

20221005 Lookup Filter charlies_stuck.xlsm
	A	B	C	D
1	Sprint	Week	Date	End
2	Sprint 6	Week 1	30/05/2022	4/06/2022
3	Sprint 6	Week 2	6/06/2022	11/06/2022
4	Sprint 6	Week 3	13/06/2022	18/06/2022
5	Sprint 6	Week 4	20/06/2022	25/06/2022
6	Sprint 6	Week 5	27/06/2022	2/07/2022
7	Sprint 7	Week 1	4/07/2022	9/07/2022
8	Sprint 7	Week 2	11/07/2022	16/07/2022
9	Sprint 7	Week 3	18/07/2022	23/07/2022
10	Sprint 7	Week 4	25/07/2022	30/07/2022
11	Sprint 7	Week 5	1/08/2022	6/08/2022
Sheet2

Cell Formulas
Range	Formula
D2:D11	D2	=C2+5
C3:C11	C3	=D2+2

 

[charlies_stuck]

OMG - legend....

 

[Alex Blakenburg]

There other formulas you can use so just let us know if we want other options.

 

[charlies_stuck]

Thanks - A few niggles getting it out of MS Project but it will work. Thanks

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[charlies_stuck]

It would be nice if you could convert it MS Project to save the export, update and import, so it would go on the fly. The MSP formulas are a bit trickier and I think you would maybe have to list out all the Sprints similar to an SQL script (maybe). Must say, I am surprised that the MSP Project Field Called "Sprint: is not dynamically linked to date (as that is to all intents and purposes a lu table)

The MSP Formula box example is

Clearly I got something right as its returning unknown

Sprints in MSP are

Maybe theres a switch i need to turn on in MSP.

In the meantime - thanks for the help thus far.

Cheers

 

[Alex Blakenburg]

I'm afraid I don't know anything about MS project. There is a section for General discussion and Other Applications, if you wanted to try it there.
It might be worth putting MS Project in the subject line so you attract people with expertise in that.