How to stop day of month propagation based on last day of month.

[Bob Serack]

Hello.
I've searched some, but sometimes you just don't know what to look for.
Here is my issue.
I have a spread sheet column for everyday of the month.
I want Mon, Tue, Wed, etc at the top in a row.
I want the day number (1, 2, 3, etc) in the next row (and 1 being in the first column).
The date of the first day of the month is somewhere on the sheet in a cell.
I have the row with Mon, Tue, etc defined as ddd.

I can start with day 1 and propagate the month with numerical and alpha day info with a simple +1 propagation. That part works just fine.
My issues are for days, 28, 29, 30, and 31.
I don't want anything to show when if the month ends before 31.
For example, if Feb 28 is the last day of the month, I don't want days 29-31 and whatever alpha day they relate to, to show.
I have tried any number of ideas. Nothing is working.
I do not have the EOMONTH function available.
So, I need to determine the last day of the month, then somehow stop the propagation.
I have tried some IF statements. No luck for me.
I'm sure this is simple, but today, I am making it difficult.
Any thoughts?
Thanks, Bob

[TMS]

Please post a sample workbook

Regards

[daddylonglegs]

So asssuming that A1 is the 1st of the month in B1 you currently just have

=A1+1

copied across

Try changing that to

=IF(A1="","",IF(DAY(A1+1)=1,"",A1+1))

and copy across.......

For row 2 you can use something like this in A2 copied across

=IF(A1="","",DAY(A1))

[Bob Serack]

Here is a sample of what I am trying to do.
Thanks ...

[daddylonglegs]

You should be able to use the method I suggested with some alterations to cell references. Try this formula in C3 copied across to AF3

=IF(B3="","",IF(DAY(B3+1)=1,"",B3+1))

then make B2 this formula

=IF(B3="","",B3)

and copy across to AF2

[Bob Serack]

DaddyLongLegs ...

Thanks for the tip ...
It worked!

Thanks again for this forum.
I'll be back.
Bob

[Bob Serack]

DaddyLongLegs ...

Pardon my intrusion ...
I know this post was closed, but ...
I have a question.
The formula worked just fine for my earlier calendar problem. And again thanks!

Now, I want to use some it in some VB code to set up and copy a range of cells.
Now, I look at the formula

=IF(B3="","",IF(DAY(B3+1)=1,"",B3+1))

and I can't figure out part of it.
The part ....
IF(DAY(B3+1)=1,"",B3+1) is my issue.
How does this work? B3+1 would never = 1, would it? B3 is = 1, so all cols past B3 are >1. So, I don't understand how it knows when it is the end of the month and runs out of days and insert a blank day.

Is there a simple way to explain this in English?!
Tell me what you can.
Thanks, Bob