Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting end of month on a monthly sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting end of month on a monthly sheet
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Contents Sheet | Excel Discussion (Misc queries) | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions | |||
Sheet Protection -- Content vs. FORMATTING | New Users to Excel | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |