Days in Month (Calender)?

[Ken]

I am creating a spread sheet with blocks of cells to
handle any month ... 7 rows (days of week) by 5 Cols (Wks
in a month) = 35 cells (days).

I would like a formula in each of these cells so I can
enter a single "date" in a seperate cell & have the
appropriate dates for the month entered into the block of
cells I have created ... unused cells to contain a "blank".

My Rows contain Days of Week ... For example ... in a
seperate cell I enter ... "10/01/03" ... & I want results
as indicated in Range B1:F7 below (note: I would also
like to put a formula in Col A to give me day of week
after range B1:F7 is filled in) ... Is this all
possible? ... Thanks ... Kha

A B C D E F

Mon blank 6 13 20 27
Tue blank 7 14 21 28
Wed 1 8 15 22 29
Thu 2 9 16 23 30
Fri 3 10 17 24 31
Sat 4 11 18 25 blank
Sun 5 12 19 26 blank

 

[J.E. McGimpsey]

One way:

A1:A7 you can hard-code as Mon-Sun, right?

B1: =IF(MONTH(C1-7)=MONTH(C1),C1-7,"")

Copy B1 down to B7.

C1: =DATE(YEAR(H1),MONTH(H1),8)-WEEKDAY(DATE(YEAR(H1),MONTH(H1),1),3)
C2: = C1+1

Copy C2 down to C7.

D1: = C1+7
E1: = D1+7
Copy D1:E1 down to D7:E7

F1: =IF(MONTH(E1+7)=MONTH(E1),E1+7,"")
Copy F1 down to F7

Format B1:F7 with Format/Cells/Number/Custom d

 

[J.E. McGimpsey]

I assumed H1 was where you were entering your date. It need't be the
first day of the month...

 

[Ken]

J.E. ... This is perfect ... Now?

How do I expand out to a 2nd month etc for an entire year
of completed dates by entering the single date in H1?

Thanks again for all this Excel Magic ... Kha

-----Original Message-----
I assumed H1 was where you were entering your date. It need't be the
first day of the month...

 

[J.E. McGimpsey]

In subsequent months, in the same position as C1:

=DATE(YEAR(H1),MONTH(H1)+n,8)-WEEKDAY(DATE(YEAR
(H1),MONTH(H1)+n,1),3)

where n = 1 to 11