#1   Report Post  
Old May 18th 05, 04:26 AM
Jerry Kinder
 
Posts: n/a
Default 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




  #2   Report Post  
Old May 18th 05, 11:07 AM
Ron Rosenfeld
 
Posts: n/a
Default

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
  #3   Report Post  
Old May 18th 05, 11:23 AM
Leo Heuser
 
Posts: n/a
Default

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






  #4   Report Post  
Old May 18th 05, 01:00 PM
Duke Carey
 
Posts: n/a
Default

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




  #5   Report Post  
Old May 18th 05, 06:41 PM
Jerry Kinder
 
Posts: n/a
Default

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





  #6   Report Post  
Old May 18th 05, 06:43 PM
Jerry Kinder
 
Posts: n/a
Default

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






  #7   Report Post  
Old May 18th 05, 08:34 PM
Ron Rosenfeld
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Howdo U copy a formula down a column, that uses data in another w. Need Help pasting a formula Excel Worksheet Functions 1 February 25th 05 06:04 PM
Howdo U copy a formula down a column, that uses data in another w. brantty Excel Worksheet Functions 0 February 25th 05 10:11 AM
Problem with VBA returning the contents of a long formula. [email protected] Excel Discussion (Misc queries) 2 February 23rd 05 12:14 AM
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM
How do I format dates accessed by a formula Mont22 Excel Discussion (Misc queries) 2 January 12th 05 04:09 PM


All times are GMT +1. The time now is 06:54 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017