Hi i have spent ages trying to work this out! and now have resorted to this forum for help.
I need a spreadsheet to auto calculate the month and no of days once a Week Commencing date has been provided. I have provide a problematic example below which shows WC 28/07/08 being part of 2 different months.
I need this so that when my colleagues come into work each monday - they can input the previous weeks activities in other columns. however, when reporting - i will be using a pivot table on the data to extract whole calender months worth of data and so need the month shown as below.
please can anyone help with the formulas i need for the month and days columns.
I HAVE INSERTED * TO SHOW SEPERATORS INCASE THE TABLE APPEARS WITHOUT SPACES:
WC (Mon - Sun) *Month *Days
28/07/08 *July *4
28/07/08 *August *3
04/08/08 *August *7
11/08/08 *August *7
18/08/08 *August *7
25/08/08 *August *7