Input columns with number of days in month

[dove_g]

I have one cell where I input month/year, and would like that Excel
automatically fill all fields in row as:

from 1 2 3 4 ..... to 28 (if it is input date: 02/2003)

and below of this each cell to put weekday.

It will be great if each column on Sunday have background
automatically colored with some color (red) and few cell below Sunday
also red background.

Thanks.

 

[Bernie Deitrick]

dove,

If you put the date 02/2003, Excel will enter it as Feb 1, 2003. Let's say you use cell A1 for that.

If you enter the 1,2,3,.....28 into cells A2:A29. Then in B2, use the formula
=$A$1+A2-1
and format as date. In cell C2, use the same formula, but format for custom "dddd"

You can use conditional formatting to change the color depending on the day. Select cell C2, use Format | Conditional formatting...
select "Formula is" and use the formula:

=WEEKDAY(C2) = 1

Set the formatting or to whatever you want.
Copy both those cells down to match your 1....28 cells.

HTH,
Bernie
Excel MVP

 

[dove_g]

If you enter the 1,2,3,.....28 into cells A2:A29. Then in B2, use the formula
=$A$1+A2-1

Yes but now I must have in one row all numbers from 1, 2 ... 31, I
dont want to have that, no matter, I appretiate your help. I use now
instead of =$A$1+A2-1, just this

=$A$1+1-1, next cell =$A$1+2-1, =$A$1+3-1 etc... to =$A$1+31-1 (thats
max, 31 day is maximum in month).
So your formula is completely the same Thanks.

and format as date. In cell C2, use the same formula, but format for custom "dddd"

Thanks for that also.

Now what I want, if one month have 30 days, I dont want that next cell
have 01, but nothing, empty cell (because this is not the same mont,
but date from next month).
So I think to put in last few cells (let say from 28 - 31, because
minimal days in month is 28), some formula which check value, if
month(cell A29)>month(cell A28) then show " ", else show dd I think
that you understand me. How to do that.

You can use conditional formatting to change the color depending on the day. Select cell C2, use Format | Conditional formatting...
select "Formula is" and use the formula:

=WEEKDAY(C2) = 1

Thanks this work great, only problem is that I have around 31 x 15
cell, and I must directl input value for each cell Can't use select
cell and drag mouse pointer, beacue Excell always add if theer is A2,
next cell must be A3, then A4 etc...) I have try to input in 3 cells
A2 but nothing, then again excel adds some "next" value. No matter, I
will do that

Thanks a lot Bernie.

 

[Bernie Deitrick]

Dove,

See my comments in-line.

HTH,
Bernie
Excel MVP

Yes but now I must have in one row all numbers from 1, 2 ... 31, I
dont want to have that, no matter, I appretiate your help. I use now
instead of =$A$1+A2-1, just this

=$A$1+1-1, next cell =$A$1+2-1, =$A$1+3-1 etc... to =$A$1+31-1 (thats
max, 31 day is maximum in month).
So your formula is completely the same Thanks.

Try entering this into cell B2
=$A$1 +Column()-Column($B$1)
(if you enter it into another column, change the B in the formula to the column letter of the cell where you are entering it)
Copy that cell to the right for 31 columns total.

Thanks for that also.

Now what I want, if one month have 30 days, I dont want that next cell
have 01, but nothing, empty cell (because this is not the same mont,
but date from next month).
So I think to put in last few cells (let say from 28 - 31, because
minimal days in month is 28), some formula which check value, if
month(cell A29)>month(cell A28) then show " ", else show dd I think
that you understand me. How to do that.

Change the formula to
=IF(Month(XXXX)=Month($A$1),XXXX,"")
where XXXX is whatever formula you ended up using.

Thanks this work great, only problem is that I have around 31 x 15
cell, and I must directl input value for each cell Can't use select
cell and drag mouse pointer, beacue Excell always add if theer is A2,
next cell must be A3, then A4 etc...) I have try to input in 3 cells
A2 but nothing, then again excel adds some "next" value. No matter, I
will do that

I'm not sure what you mean, but if you first select your 31X15 cells, then use the same formula, Excel will automatically increment
the C2 reference properly.

Thanks a lot Bernie.

You're welcome.