Activity Planning

**SParker0285** · 11-28-2011, 01:47 PM

We currently use Excel to plan our assignments (in a Gantt Chart format). Our worksheets are set up so that dates serve as column headings and activities for each assignment serve as row headings. From time to time, a new assignment comes in that is urgent. We must then insert this new assignment and push all other assignments to the right (push the dates out).

The problem we're having is that to do this, we must re-enter all the data or move it manually around holidays, training days, and planned vacations (we plan our assignments for the entire year).

Is there a way in Excel to freeze certain columns so that when we insert a new assignment and push existing assignments to the right, the existing assignments skip over holidays, training days, and vacation days?

**Whizbang** · 11-28-2011, 02:38 PM

Please upload a sample workbook to better enable us to help you. I am familiar (somewhat) with Gantt charts, but not familiar with how you would have implemented it. Uploading a workbook will allow me to give you an answer specific to your needs.

**SParker0285** · 11-30-2011, 10:58 PM

I attached a sample time-phasing worksheet. I took out all the detail for obvious reasons, and made a simple worksheet that I believe shows what we are trying to do. Please let me know if I need to explain anything further and thank you guys for your help.

**Whizbang** · 12-01-2011, 10:23 AM

The attached workbook will get you close, I think, to your desired result. It is not equiped to handle tasks whose "Hours Remaining" are not factors of 8. I would need to work on that a bit to accomodate less than full day tasks.

To add a task 4, simply copy the row above and paste special as formulas. You may want to then apply a conditional formatting of your choosing.

Also, notice that I un-merged the holiday and vacation cells. Merging, you will soon find, will often complicate or prevent calculations. It is usually best to avoid merging unless the cells involved are only for display purposes and will never be included in any sort of calculation.

I inserted a column after "Scheduled Hours". This is merely a place-holder to allow for a starting point. You can hide this column, if you desire.

The formula in the new G2 is:
=IF(ISERROR(MATCH(G$1,Holidays,0)),IF(SUM($F$2:F$4)-SUM($F2:F2)=SUMIF($C$2:$C$4,"<" & $C2,$D$2:$D$4),MIN($D2-SUM($F2:F2),8-SUMIF($C$2:$C$4,"<" & $C2,G$2:G$4)),0),"X")

I then copied this formula down and accross.

***Edit***

Oh, and I added a priority column. Duplicate priority numbers will cause the sheet to not calculate correctly, but this should allow for a quick and easy way to push back tasks.