Return week number within specified date ranges.

[IIII]

Hi All -- I am just looking for some help on the following issue -:

I am trying to find a way where, I can return the week number, within a specified date range, based off an entered date.

In the example below, if I enter a date in column E, column F will return the Period for which that date falls under and column G should return the week number, from within the corresponding date range.

Date return example.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ID #	First name	Last Name	Referred to	Initial referred date	Period	Week		Start	End	Period
2	123456	Jane	Doe	John Doe	08/02/2021	1	2		01/02/2021	12/04/2021	1
3	123457	Jane	Doe	John Doe	07/05/2021	2	3		20/04/2021	29/06/2021	2
4	123458	Jane	Doe	John Doe	01/09/2021	3	8		05/07/2021	13/09/2021	3
5									23/09/2021	02/12/2021	4
6
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=IF(AND(E2>=I2,E2<=J2),K2,IF(AND(E2>=I3,E2<=J3),K3,IF(AND(E2>=I4,E2<=J4),K4,IF(AND(E2>=I5,E2<=J5),K5,"Outside period"))))

Sorry if this doesn't make sense, please do not hesitate to ask any clarifying questions.

Thanks in advance for any help provided.

Cheers.

 

[Saba Sabaratnam]

Hi,

You may want to enter the following formula in G2 and copy it down.

=ROUNDUP((DATEDIF(I2,F2,"d")+1)/7,0)

It gives correct answer for the first two records. But it gives 9 for the third record as there are 58 days between 'Start' and the "initial referred date". When you divide it by 7 and round it up, it will give week 9 instead of week 8.

Kind regards

Saba

 

[footoo]

=ROUNDUP((E2-I2+1)/7,0)

 

[Phuoc]

Try this the formula in F2:

=IFERROR(LOOKUP(2,1/(E2>=$I$2:$I$5)/(E2<=$J$2:$J$5),$K$2:$K$5),"Outside period")

 

[Phuoc]

and in G2:

=IF(ISNUMBER(F2),CEILING(E2-INDEX($I$2:$I$5,MATCH(F2,$K$2:$K$5,0))+1,7)/7,"")

 

[IIII]

Try this the formula in F2:

=IFERROR(LOOKUP(2,1/(E2>=$I$2:$I$5)/(E2<=$J$2:$J$5),$K$2:$K$5),"Outside period")

Thank you for fixing this one up also.

 

[IIII]

and in G2:

=IF(ISNUMBER(F2),CEILING(E2-INDEX($I$2:$I$5,MATCH(F2,$K$2:$K$5,0))+1,7)/7,"")

Thank you all for sending through your ideas. And thanks @Phuoc, the solution you provided worked like a charm. Appreciate it.

Cheers.

 

[Phuoc]

You're welcome! Glad it worked.