In Excel I have two columns of dates (day/month/year) for the start date and end date of an activity for a construction schedule. There are multiple activities and a timeline of months at the top of my sheet.
I would like to be able to input all the start and end dates into the two columns and have my top row automatic adjust to the earliest month to the latest month.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010
feb/2010___aug/2010
jun/2010___nov/2010
So when I change any of the start or end dates, my top row timeline will adjust and add month columns or decrease month columns to match my start and end dates.
Also, once I input the start and end dates, I would like the corresponding cells for that row to highlight/activate for the duration of that activity.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010______x__x___x___x___x___x___x
feb/2010___aug/2010_________x___x___x___x___x___x___x
jun/2010___nov/2010_________________________x___x___x___x___x___x
On the right side of those rows there is a total cost for the duration of the activity. In each highlighted/active cell I want to take the total cost for that row divided by each month for that activity and have cost that distributed into each month.
For example:
start______end__________jan_feb_mar_apr_may_jun__jul_aug_sept_oct_nov
jan/2010___july 2010_____20__20__20__20__20__20__20____________________140
feb/2010___aug/2010_________40__40__40__40__40__40__40________________280
jun/2010___nov/2010_________________________50__50__50__50__50__50____300
I realize its a complex set of formulas that tie into each other, but i am a novice excel user and there are many more experts out there that i figure could provide me insight. I appreciate your time in looking into this matter!
Please respond if you know how to any of these steps and the forumlas needed for them.
Thanks,
PMB
Bookmarks