Evaluating a start and an end date

[Mark]

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…..

 

[Bernie Deitrick]

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]

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]

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]

Works great...THANKS !!!!

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