# Return week number within specified date ranges.

#### IIII

##### New Member
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
ABCDEFGHIJK
1ID #First nameLast NameReferred toInitial referred datePeriodWeekStartEndPeriod
2123456JaneDoeJohn Doe08/02/20211201/02/202112/04/20211
3123457JaneDoeJohn Doe07/05/20212320/04/202129/06/20212
4123458JaneDoeJohn Doe01/09/20213805/07/202113/09/20213
523/09/202102/12/20214
6
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Saba Sabaratnam

##### Board Regular
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

##### Well-known Member
=ROUNDUP((E2-I2+1)/7,0)

#### Phuoc

##### Active Member
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

##### Active Member

and in G2:

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

#### IIII

##### New Member
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

##### New Member
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.

Replies
21
Views
419
Replies
1
Views
140
Replies
6
Views
253
Replies
2
Views
407
Replies
3
Views
212

1,127,298
Messages
5,623,843
Members
415,995
Latest member
SergioCM92

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back