A list of days, by date, to the end of the next month - then the list turns to months

Hello all - first post (I think) so here's hoping someone out there might be able to help me.

Here is my scenario:

I am working on a spreadsheet for work. The file will be updated every Monday so the file will be laid out like this:

Cell A1 = Date (of the Monday of that week) - so today would be 21 Feb 2011

Row A2 to AM2 would take me out to 31 Mar 2011

Then I would like to see AN = April, AO = May, AP = June, AQ = July

Now, I can do this today.

However when I change the date for the file next week to Feb 28 2011 I will then run into April 1st to the 7th before Cell AN which will display April as a monthly 'bucket'.

So - what I'd understand is this:

I want to look out 6 months. 2 months in days, 4 months in months.

Is there a way Excel can show dates out to the end of month 2 and then show 4 months - regardless of the date you put into Cell A1? I want to avoid having some days showing for a month which also has a Cell to its own.

Hopefully I've explained that one OK.

Any help would be great.

Many thanks

Originally Posted by Andy Pope

Can you explain what you expect to see when the date in A1 is 21st Mar 2011.

I don't think you have enough columns to complete the dates in April before the summary months of May/June/July/August

Hi Andy,

Right now I would see:

Cell A1 = 21 March 2011

Cell A2 = 21 March 2011, Cell B2 = 22 March 2011 and so on

Up until Cell BJ = 21 May 2011 (i.e. 62 days from now = 2 months which is the most I would ever need to look out)

However, ideally, I'd like Excel to 'stop' at Cell AO = 30 April 2011 with Cell AP = May, Cell AQ = June and so on.

i.e. just show the rest of the 'days' in current month + next month, then revert to monthly cells for 4 more months.

I've attached a file which might helo to explain a bit more.

Cheers.
MD

Originally Posted by brokenbiscuits

Is this the sort of thing you're after?

(Please see attached)

EDIT: Looking at your attached file, looks like it should do the job - just have a tinker with the conditional formatting and you should be set. Remember to make sure you pull the formula along 66 columns (max 2x31 days + 4 months). Hope that helps.

Hi there,

Firstly - thanks to you, and to all, for their help with this so far. I've not had a chance to get online recently.

So far what you've posted appears to close to what I am looking for.

However - one thing I can't work out is this: if I change the date in Cell A1 on the file you created nothing changes elsewhere on the file. I'd expect the row of dates to change.

The formula you've put in is for me - I am not really sure how it works so I am not clued up enough to update it to suit.

Would you be able to help? Any idea why that's happening?

Again thanks.

MD

Originally Posted by Andy Pope

If you are using brokenbiscuits' example file then you will need to press F9 as the calculation is set to manual in that file.

To change to automatic use
Office > Excel Options > Formula > Calculation Options > Automatic

or Formulas Tab > Calculation > Calculate Options > Automatic

Andy - many thanks. Got it to work.

Thanks again to you all - excellent help for me. Looks like the spreadsheet works

Here's me thinking I am not bad on Excel some of those formulas are unreal.

Cheers all,
MD