Calendar date from formula

[SalientAnimal]

Hi all,

I'm not really sure how to explain this, but what I am trying to achieve is to get a calendar layout using an array formula. The formula I'm using is from a workbook that I had found on the web and it works perfectly. It was in a general calendar layout when I downloaded it and I have adapted it to run from day 1 - the last day in a month from left to right.

My starting day for each week is Monday (the original workbook used Sunday). The problem though is, when I get to a month that starts on a Sunday, it starts populating from the first Monday, and thus losing the 1st day of that month. I have attached an example.

Thanks.

[daddylonglegs]

Are you particularly wedded to the single array formula approach for each month?

If not then you could use this ordinary formula in C7 copied across and repeated on your other rows

=IF(MONTH($B7-WEEKDAY($B7-1)+COLUMNS($C7:C7))<>MONTH($B7),"",$B7-WEEKDAY($B7-1)+COLUMNS($C7:C7))

It doesn't use the header row for the days - if you want a Sunday start just take out the two -1s highlighted (and change the header row).

See attached

If you want it to work with the array formula I can fix that too, just post back.......

[SalientAnimal]

Hi there,

Yes, I would prefer to use the array formula. With that said though, I am always looking to learn more, and if you would mind perhaps explaining the other formula you suggested. Might just help me in a future solution :-).

Thanks, your help is really appreciated.

[SalientAnimal]

Hi there,

Have you been able to come up with a solution to the array as yet?

Sorry don't mean to be a pain.


I have now also made an change to the formula that I need. I have used the suggest formula =IF(MONTH($B7-WEEKDAY($B7-1)+COLUMNS($C7:C7))<>MONTH($B7),"",$B7-WEEKDAY($B7-1)+COLUMNS($C7:C7)), however, I have now added two columns after the Sunday for totally, which now throws out the date calculation.

Any advice on fixing this.

Thanks.