formatting end of month on a monthly sheet

K

kjharris123

:confused: I'm working on a simple Excel doc that is used to take
readings on power usage three times a day for every day of the month.
The month and year is entered at the top of the sheet, and the day
cells in the sheet will format fine. The sheet has enough room for
all 31 days of the month. My question is, is there any way to write
the formula that if the month only has 29 days the last two date boxes
(30and 31) blank?

Thanks,
Kjharris
 
B

Biff

Hi!

How are you entering the month and year? In separate cells? How are you
entering the month? As a number? As an abbrieviation like Dec? Are the days
of the month the actual dates or are they just a series of numbers from 1 to
31? Do the days go down a column or across a row?

Biff

"kjharris123" <[email protected]>
wrote in message
news:[email protected]...
 
K

kjharris123

:rolleyes: Thanks for a quick reply. At the top of the sheet (K1) th
user will enter the month and year in the same cell. For each da
entry, the formula was just (K1+1), (K1+2) etc., and the day cel
format is (12/1/2005). I have the page formatted in two columns 1-16t
and 17th – 31st in the second column
 
B

Biff

Hi!

I'm having a hard time trying to figure out how you can enter a month and
year in a cell and then use a fomula like =K1+1 to get the first day of that
particular month/year.

But anyhow.......

Let's assume you enter a full date in K1: 12/1/2005, and that it's just
formatted to display as mmm-yy (or some format like that)

The easiest way I can think of to do this is to use conditional formatting
on the last 3 cells that represent the 29th, 30th and 31st of any month.

This requires that the Analysis ToolPak add-in be installed.

Create a named formula:

Goto Insert>Name>Define
Name: Lastday
Refers to: =EOMONTH(Sheet1!$K$1,0)
OK

Use your actual sheet name in that formula.

Now, select the last 3 cells that represent the 29th, 30th and 31st of the
month. I'll use cells K16:K18 in this example.

Select the range K16:K18
Goto Format>Conditional Formatting
Formula is: =K16>Lastday
Click the Format button
Set the font color to be the same as the background color.
OK out

Biff

"kjharris123" <[email protected]>
wrote in message
news:[email protected]...
 
Top