formatting end of month on a monthly sheet
 

: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


--
kjharris123
------------------------------------------------------------------------
kjharris123's Profile: http://www.excelforum.com/member.php...o&userid=29715
View this thread: http://www.excelforum.com/showthread...hreadid=494306

formatting end of month on a monthly sheet
 

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"
wrote in message
...

: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


--
kjharris123
------------------------------------------------------------------------
kjharris123's Profile:
http://www.excelforum.com/member.php...o&userid=29715
View this thread: http://www.excelforum.com/showthread...hreadid=494306

formatting end of month on a monthly sheet
 

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


--
kjharris123
------------------------------------------------------------------------
kjharris123's Profile: http://www.excelforum.com/member.php...o&userid=29715
View this thread: http://www.excelforum.com/showthread...hreadid=494306

formatting end of month on a monthly sheet
 

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 InsertNameDefine
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 FormatConditional Formatting
Formula is: =K16Lastday
Click the Format button
Set the font color to be the same as the background color.
OK out

Biff

"kjharris123"
wrote in message
...

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


--
kjharris123
------------------------------------------------------------------------
kjharris123's Profile:
http://www.excelforum.com/member.php...o&userid=29715
View this thread: http://www.excelforum.com/showthread...hreadid=494306

formatting end of month on a monthly sheet
 

You might be able to use the DATE function to do some of what you want.

"kjharris123"
wrote in message
...

: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


--
kjharris123
------------------------------------------------------------------------
kjharris123's Profile:
http://www.excelforum.com/member.php...o&userid=29715
View this thread: http://www.excelforum.com/showthread...hreadid=494306