Need to count how many days in week period, if period starts mid week?

Hi -

Are you sure you attached the correct file? The spreadsheet doesn't seem to correspond to your description.

As loginjmor has said, I think your file's wrong - or it doesn't work without the links to another file which it appears to have.

In the absence of the file, here's a suggestion:
Create a separate sheet with a list of all the dates - i.e. 52 rows. Each row should have the period, week, start date, end date and number of days:
Period | Week | Start Date | End Date | Days
Your first start date is easy (1/4/2016). Though I'd recommend you put the year in another cell (let's say B1) and use this instead:

Formula:

Please Login or Register  to view this content.

The end date of the first week is trickier, but you can get the date of the first Sunday in April by using this formula (with 2016 in cell B1):

Formula:

Please Login or Register  to view this content.

The start and end date of weeks 2 to 51 are also easy - just add 1 to the previous end date, then 7 to the start date.

The end date of week 52 (period 13 week 4) is again easy (31/3/2017), though again I'd recommend using a reference instead of a set year, like this (with 2017 in C1):

Formula:

Please Login or Register  to view this content.

You can then get the number of days for each period by subtracting the start date from the end date and adding 1 to make the number inclusive.

I assume it's ok for the last 'week' (period 13 week 4) to be up to 14 days long (when 1 Apr falls on a Sunday)?

Then, on the sheet you need the data on, use a Lookup to extract the number of days per period/week.

Attached is a file showing this all working - I've used VLOOKUP on two example results sheets, but INDEX/MATCH would be more flexible depending on exactly what you want to achieve where. The cells into which you need to enter data (start year of financial year, or period) are shaded yellow.

I hope that's of some help.

Thanks for the feedback and the rep.