Evaluating a start and an end date
 

I have a spreadsheet that emulates Microsoft Project where the first column
has the activities and the hours go across in the columns, by month. The
engineers have already time phased each line item activity and now I need to
calculate the start date and the end date for each activity based on where
the time phased hours are placed (in what month). I'm assuming that all
activities that have hours are contiguous hours.

Start End Apr-08 May-08 Jun-08
Activity A ???? ??? 150hrs 120hrs 10hrs
Activity B€¦.


If at all possible, Id like to resolve this without VB€¦.but lets see what
you folks can come up with. Thanks in advance€¦..

Evaluating a start and an end date
 

Mark,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($1:$1,1,MIN(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2),1000)))
=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2))))

Assumes your dates are in row 1, labels in column A, and any "non-hour" cells are blank.

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I have a spreadsheet that emulates Microsoft Project where the first column
has the activities and the hours go across in the columns, by month. The
engineers have already time phased each line item activity and now I need to
calculate the start date and the end date for each activity based on where
the time phased hours are placed (in what month). I'm assuming that all
activities that have hours are contiguous hours.

Start End Apr-08 May-08 Jun-08
Activity A ???? ??? 150hrs 120hrs 10hrs
Activity B..


If at all possible, I'd like to resolve this without VB..but let's see what
you folks can come up with. Thanks in advance...

Evaluating a start and an end date
 

Thanks......

I did a mock up and the End date worked, but the Start result was just the
text string "Start".

Here is the result....

Start End Apr-08 May-08 Jun-08 Jul-08
Activity A Start May-08 160 160
Activity B Start Jul-08 160 160
Activity C Start Jun-08 50 50



"Bernie Deitrick" wrote:

Mark,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($1:$1,1,MIN(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2),1000)))
=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2))))

Assumes your dates are in row 1, labels in column A, and any "non-hour" cells are blank.

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I have a spreadsheet that emulates Microsoft Project where the first column
has the activities and the hours go across in the columns, by month. The
engineers have already time phased each line item activity and now I need to
calculate the start date and the end date for each activity based on where
the time phased hours are placed (in what month). I'm assuming that all
activities that have hours are contiguous hours.

Start End Apr-08 May-08 Jun-08
Activity A ???? ??? 150hrs 120hrs 10hrs
Activity B..


If at all possible, I'd like to resolve this without VB..but let's see what
you folks can come up with. Thanks in advance...

Evaluating a start and an end date
 

Mark,

Change the B in both of the the B2:IV2 addresses to the letter of the first column of -possibly-
entered hours. If column F is the first, then use F2:IV2.....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Thanks......

I did a mock up and the End date worked, but the Start result was just the
text string "Start".

Here is the result....

Start End Apr-08 May-08 Jun-08 Jul-08
Activity A Start May-08 160 160
Activity B Start Jul-08 160 160
Activity C Start Jun-08 50 50



"Bernie Deitrick" wrote:

Mark,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($1:$1,1,MIN(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2),1000)))
=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2))))

Assumes your dates are in row 1, labels in column A, and any "non-hour" cells are blank.

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I have a spreadsheet that emulates Microsoft Project where the first column
has the activities and the hours go across in the columns, by month. The
engineers have already time phased each line item activity and now I need to
calculate the start date and the end date for each activity based on where
the time phased hours are placed (in what month). I'm assuming that all
activities that have hours are contiguous hours.

Start End Apr-08 May-08 Jun-08
Activity A ???? ??? 150hrs 120hrs 10hrs
Activity B..


If at all possible, I'd like to resolve this without VB..but let's see what
you folks can come up with. Thanks in advance...

Evaluating a start and an end date
 

Works great...THANKS !!!!



"Bernie Deitrick" wrote:

Mark,

Change the B in both of the the B2:IV2 addresses to the letter of the first column of -possibly-
entered hours. If column F is the first, then use F2:IV2.....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Thanks......

I did a mock up and the End date worked, but the Start result was just the
text string "Start".

Here is the result....

Start End Apr-08 May-08 Jun-08 Jul-08
Activity A Start May-08 160 160
Activity B Start Jul-08 160 160
Activity C Start Jun-08 50 50



"Bernie Deitrick" wrote:

Mark,

Array enter (enter using Ctrl-Shift-Enter)

=INDEX($1:$1,1,MIN(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2),1000)))
=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(B2:IV2)),COLUMN( B2:IV2))))

Assumes your dates are in row 1, labels in column A, and any "non-hour" cells are blank.

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I have a spreadsheet that emulates Microsoft Project where the first column
has the activities and the hours go across in the columns, by month. The
engineers have already time phased each line item activity and now I need to
calculate the start date and the end date for each activity based on where
the time phased hours are placed (in what month). I'm assuming that all
activities that have hours are contiguous hours.

Start End Apr-08 May-08 Jun-08
Activity A ???? ??? 150hrs 120hrs 10hrs
Activity B..


If at all possible, I'd like to resolve this without VB..but let's see what
you folks can come up with. Thanks in advance...