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

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?

--
Rick (MVP - Excel)


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

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" wrote:

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?

--
Rick (MVP - Excel)


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

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.

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


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

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


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

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 (MVP - Excel)


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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


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