Macro start and finish date from rows

[Marylou]

is there a macro that will put the start date and finish date for each of
these activities as the 1st of the month is good:

example

task start finish 01-Jan-01 01-Feb-01 01-Mar-01 01-Apr-01
activity1 01-Jan-01 01-May-01 100 200 300 100
activity2 01-Apr-01 01-Apr-01 200
activity3 01-Mar-01 01-Apr-01 100 100
activity4 01-Jan-01 01-Apr-01 300 200 300
activity5 01-Jan-01 01-Mar-01 10 300
activity6 100 200 300

 

[Rick Rothstein]

Your question is not entirely clear (which may be due to the way my
newsreader has formatted your example data)... can you provide a little more
information about what you have, where you have it and what you want it to
look like afterwards?

 

[Marylou]

Thank you Rick

Basically what I need to do is:

Our people plan their work in excel by putting a task and then allocating
hours in each month they will be working. Sometimes they skip a month or two
and then restart. I need to look at each row and see the first month the
hours appears in and put this a start date. Then I need to look at which
month their hours end and put this as the finish date. The only way I know
how to do it is manually. The end result is to do a quick view in MS project
gantt.
Hope this is clear.

 

[Rick Rothstein]

Use these array-entered** formulas where indicated and then copy them
down...

B2: =INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))
C2: =INDEX(D$1:Z$1,MAX((D2:Z2<>"")*COLUMN(D2:Z2))-3)

**commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

Change the last column from the Z's that I used to your actual last date
column.

 

[Marylou]

Hi Rick, I am sorry I don't understand. Do I copy your formula into columns
b2 and c2?

 

[Rick Rothstein]

Select B2 and then copy/paste this formula into the **Formula bar** at the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it. Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<>"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start and
end dates should now fill in.

 

[Marylou]

FABULOUS THANK YOU IT WORKS PERFECTLY! /Mary Lou

Select B2 and then copy/paste this formula into the **Formula bar** at the
top of the grid...

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

and then (and this is important), press Ctrl+Shift+Enter to 'enter' it. Then
select C2 and do the identical procedure with this formula...

=INDEX(D$1:Z$1,MAX((D2:Z2<>"")*COLUMN(D2:Z2))-3)

Now select both B2 and C2, then hover the cursor over the little black
square in the bottom right corner of the selection. When the cursor turns to
a small black "cross", click and drag the selection down to the row where
the last activity is located, then release the mouse button. Your start and
end dates should now fill in.

 

[Rick Rothstein]

You are quite welcome. Just checking to be sure... you did remember to
change the Column Z reference I used in my example to a column designation
that would cover all your possible data, right? Also, a side note about
array-entered formulas... if you ever have to modify one in the future, you
must recommit the formula using Ctrl+Shift+Enter and never just Enter by
itself.

 

[Marylou]

Good afternoon (Montreal time)

Thank you again; I understand column Z reference. I did not know who to do
this type of fomula. You explained exactly how I need to do this. It is great
- passed it on to my colleagues here. Some of them are programmers but did
not know this function. Really appreciate the help.

Now I need to learn how to use this discussion group - cannot seem to sort
in date order so I could not find your reply till now.

/Mary Lou

 

[Rick Rothstein]

You should consider using a news reader like Outlook Express or Window Mail
(which is Vista's version of Outlook Express)... the interface is much
easier to use (sorting by date, grouping threads, highlighting only your
threads, etc.). To do this, you would create an account
(Tools/Accounts/Add/Newsgroup) for the Microsoft public server. Use
msnews.microsoft.com as the server name and I would suggest you *not* use
your real email address in order to avoid getting on spam lists. Once your
account is set up, you can subscribe to whatever newsgroups you want (use
the search box the subscribe dialog provides to narrow the huge list down to
the groups you want... for example, type excel in the search box). After
doing this, the newsgroup will appear in a listing along with your email
accounts and you interact with it in virtually the same way.

 

[cenx]

Rick you rule!

 

[alanglloyd]

On Jul 19, 12:34�am, "Rick Rothstein"

=INDEX(D$1:Z$1,MATCH(1,--ISNUMBER(D2:Z2),))

<snip>

What is the effect of the "--" (minus,minus) operator. I've not met
that before.

Alan Lloyd

 

[alanglloyd]

Double unary minus:
� a..http://www.mcgimpsey.com/excel/formulae/doubleneg.html
� b..http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks - its somewhat esoteric - ABS() would be much clearer. As a
programmer who believes that clarity is all, I would never use an
obscure language-dependant operator <g>.

BTW b directs me to some domain-name seller.

Alan Lloyd

 

[alanglloyd]

If you're only trying to distinguish 0 from 1, then ABS() would work, but
that doesn't work in other cases where -- works, such as converting a text
representation of a number (including negative numbers) into a real number.
There are many other options, including the use of +0 or *1. �Thechoice of
which (if any) to use is with the user, of course.

ABS() works for for me (Excel 97 s2) for simple conversion of positive
or negative text ("" or ') to a real number.

Particularly as in Excel functions (where comments in the code is not
possible) I think clarity is essential (even going to hidden columns
instead of very complex functions).

For those who are expertly or MVPly helping inexperienced users, I
think that they have a duty to be explicitly clear in their advice.

Alan Lloyd

 

[David Biddulph]

Well, I'm not replying MVPly, and some would argue that I'm not
advising expertly either, but my advice would be not to use ABS() in
place of the double unary minus, because if my text number is a
negative one ABS turns it positive while the double unary minus leaves
it negative.