automatic date distribution and cost loading formulas for construction schedules

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

Sorta like this?

Thanks! I will look through it and try to apply it to the complex spreadsheet I already have... Thanks again for you time.

It looks great! Everything seems to be working out.

The one other thing I was wondering and maybe you can help me out. All my cells have a colored fill in them. Is there something I can add to the formula to unfill/white fill the cells that have an amount in them from the middle formulas?

Thanks.

Yes, just use conditional formatting. The example I posted earlier uses that to color your schedule Gantt Chart style. If you need help with this, please post a sample.

Part 1 of 2:
So i tried to change the cell formatting to be white when there is a true conditional formula by going to the "format" button on the menu bar and selecting "conditional formatting." I clicked the format button and selected the background pattern as white, but when i dragged it across it changed them all to white and I couldn't even fill them to another color. "Condition 1" read "Formula is" = then I clicked the cell I was on.

Is there a different condition i need in there. I just want the active true cell to have a white background and all the others the have the fill color they were given.

Thanks!

Part 2 of 2:
Also, I have the total number of cummulative months in the row above the actual months. 1,2,3,4,5,etc... I would like these to count along with the months as they are added or reduced so it was automatic like the months are.

So if the total duration was from Jan to May, there would a corresponding number above the month and it would count from 1 to 5.

See blue row in attachment.

Thanks!

Part 2 was easy if I understand you correctly. In F1, enter the following and copy it across:

Please Login or Register  to view this content.

Didn't understand Part 1. The file as it currently exists shows blank cells in the schedule as white and active cells (with numbers in them) in yellow. What are you trying to do differently? Maybe you're doing something that's not in the example you last posted?

Originally Posted by bentleybob

Sorta like this?

This has been very helpful to me in a project I have been working on. I have a slightly more complex project that I need some assistance with. I have projects with different durations (including different start and end dates) with a budgeted amount of labor hours allotted to the project. I need to be able to spread those hours throughout the project, based on the start and end date, but also based on a weighted percentage depending on what phase of the project. For example, I have three different types of labor. Each type of labor is spread differently. Engineering is flat, where the hours are evenly distributed across all months. Design is 65% in the first third of the project, 25% in the 2nd third of the project, and 10% in the last third of the project. And Testing is 5% in the 1st third, 75% in the 2nd third, and 20% in the last 3rd. Each category is allotted its own budget, and I want to make the spreadsheet automatically spread the hours. I have used the formula you provided in order to get the hours in the right months (based on the start/end date). I just now need to figure out how to weight the hours based on which of the 3 phases of the project it is in. Is there a solution for this type of complexity?