Formula to search what week number a given date falls in

[haug]

Hi,

I have a list or orders and the delivery date for the order. I also want the week number that the order is being delivered in. E/g/ If an order is being delivered on 26/03/2014, I want it to check what date range it falls within (24/03/14 - 30/03/14) and return the corresponding week Number (Week 21). I do have the first date of each week and that week number in another sheet in the work book - with week numbers on Row 2 and the Monday date in each week below in Row 3.

I want to be able to change the delivery date and have the week number update automatically.

Any ideas?

Example attached.

[ChemistB]

=WEEKNUM(date, ref option) look up in help to get ref options.