dates formula
Col A is Jan, Col B is Feb, etc. to Dec.
in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK |
On Wed, 18 May 2005 03:26:05 GMT, "Jerry Kinder"
wrote: Col A is Jan, Col B is Feb, etc. to Dec. in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK Select the range A3:L32 Format/Conditional Formatting Formula Is: =MONTH(A3)<MONTH(A$2) (note the "$" in the last function) Format/Font Color "White" OK/OK --ron |
Jerry
One way: In A2 (formula on one line) =IF(ROW()-ROW(A$2)+1DAY(DATE(2005,COLUMN()- COLUMN($A2)+2,0)),"",ROW()-ROW(A$2)+1&" "&A$1) Copy A2 to B2:L2 with the fill handle (the little square in the lower right corner of the cell) Copy A2:L2 to A32:L32 with the fill handle. If you want to get rid of the formulae, then, while A2:L32 is selected, do a Copy (<Ctrlc), select Edit Paste special and choose "Values", OK. Finish with <Esc and clicking a cell. -- Best Regards Leo Heuser Followup to newsgroup only please. "Jerry Kinder" skrev i en meddelelse m... Col A is Jan, Col B is Feb, etc. to Dec. in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK |
Put this formula into A2 & then copy to all your cells
--requires the Analysis Toolpak add-in =(ROW(A2)-1)*((ROW(A2)-1)<=DAY(EOMONTH(DATEVALUE(A$1&" 1, 2005"),0))) "Jerry Kinder" wrote: Col A is Jan, Col B is Feb, etc. to Dec. in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK |
Hi Ron,
Sorry I get nothing from this. I do not understand how this works. JK "Ron Rosenfeld" wrote in message ... On Wed, 18 May 2005 03:26:05 GMT, "Jerry Kinder" wrote: Col A is Jan, Col B is Feb, etc. to Dec. in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK Select the range A3:L32 Format/Conditional Formatting Formula Is: =MONTH(A3)<MONTH(A$2) (note the "$" in the last function) Format/Font Color "White" OK/OK --ron |
Hi Duke,
I guess I do not have the toolpak because this did not work. Thanks, JK "Duke Carey" wrote in message ... Put this formula into A2 & then copy to all your cells --requires the Analysis Toolpak add-in =(ROW(A2)-1)*((ROW(A2)-1)<=DAY(EOMONTH(DATEVALUE(A$1&" 1, 2005"),0))) "Jerry Kinder" wrote: Col A is Jan, Col B is Feb, etc. to Dec. in A2 is 1 Jan in B2 is 1 Feb and so on. I want to drag down from row 2 to 32 but want each month to stop displaying dates when the end of each Month comes. Jan has 31 days and Feb has 28 so Feb will display nothing in the last few cells not start with March. I use =A2+1 in A3 etc. across the top and drag all columns down at one time so I do not have to do each column one at a time. Thanks, JK |
On Wed, 18 May 2005 17:41:42 GMT, "Jerry Kinder"
wrote: Hi Ron, Sorry I get nothing from this. I do not understand how this works. JK To understand how it works, look up Conditional Formatting in HELP. The Conditional Formatting is turning the Font white (or whatever your background color is) so that dates that are not in the same month are not displayed -- which is what you requested. To get to the Conditional Formatting option, if you look on the top menu bar, you should see a menu item called "Format". If you click on that, in the drop-down menu you should see an item called "Conditional Formatting". The remainder of my instructions should be clear (I think). But if you still do not understand the instructions I posted, let us know exactly what you tried to do, and what happened when you tried it. --ron |
All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com