Default IF Date Is between 2 Dates then output additional column

[BayEnder111]

Hi Guys,

I'm struggling to wrap my head around an issue I'm having wher
basically I've got the below scenario on a spreadsheet:

Start Date - 14/03/2013 (Cell A2)

I want to look up this date against a 52 week structure to see where i
is in our "working calendar year". I've got a table which has th
following fields:

Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1

This table follows the same format for all 52 weeks.

Now in this example "Start Date" falls between the "Week Start" an
"Week End" date so I want to output Week 1 in cell B2. However I coul
have a "Start Date" of 29/03/2013 that falls in week 3 of the workin
calendar.

Is there a way to do this? I can't seem to get my head around it.

Thanks Guy

 

[Ron Rosenfeld]

Hi Guys,

I'm struggling to wrap my head around an issue I'm having where
basically I've got the below scenario on a spreadsheet:

Start Date - 14/03/2013 (Cell A2)

I want to look up this date against a 52 week structure to see where it
is in our "working calendar year". I've got a table which has the
following fields:

Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1

This table follows the same format for all 52 weeks.

Now in this example "Start Date" falls between the "Week Start" and
"Week End" date so I want to output Week 1 in cell B2. However I could
have a "Start Date" of 29/03/2013 that falls in week 3 of the working
calendar.

Is there a way to do this? I can't seem to get my head around it.

Thanks Guys

Why use a table? If I understand you correctly, you could use a simple formula:

="Week " & INT((A2-CalendarStart)/7)+1

Where CalendarStart is the first day of the first week in your "working calendar year"