Need a Formula to Lookup the week number that a day falls under between two dates

[Robnrolla]

Week

Day

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

From:

28 May 17

29 May 17

30 May 17

31 May 17

1 Jun 17

2 Jun 17

3 Jun 17

4 Jun 17

5 Jun 17

6 Jun 17

7 Jun 17

8 Jun 17

9 Jun 17

10 Jun 17

To:

28 May 17

29 May 17

30 May 17

31 May 17

1 Jun 17

2 Jun 17

3 Jun 17

4 Jun 17

5 Jun 17

6 Jun 17

7 Jun 17

8 Jun 17

9 Jun 17

10 Jun 17

Week

10

11

From:

28 May 17

4 Jun 17

To:

3 Jun 17

10 Jun 17

<colgroup><col><col span="4"><col span="9"><col></colgroup><tbody>
</tbody>

I need a formula to go from B1:O1 that will lookup the day in the week range and find the week number.

I originally tried an If formula with an Index to find greater than the From date and less than the To date and then an index match match to pull back the week number. This did not work for me. Perhaps i did this wrong?

Can anyone please help me?

Thanks,

 

[mole999]

there is a function for that > =WEEKNUM(A1) there must be a modification to do the financial year

 

[barry houdini]

You can lookup the date in the from dates and find the corresponding week number above, e.g. if from dates are in B7:Z7 and corresponding week numbers in B6:Z6 then use this formula in B1 copied across

=LOOKUP(B2,$B7:$Z7,$B6:$Z6)​