| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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€¦.. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
| How to programatically determine the start date and end date of a graph | Charts and Charting in Excel | |||
| Minimum Date Greater Than Observation Start Date | Excel Discussion (Misc queries) | |||
| Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
| how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
Linear Mode
