Autogenerate a sequence of week including cell formatting

[nlegokloz]

Hi

I am making a kind of a gann't chart in excel (Yeah I know I should use Project instead but not everybody in the company can do that) for follow-up on projects and tasks. When making a new one atm. I manually have to change the weeks and the months containing the weeks to match the start month and duration. Then I have to change the formatting afterwards. Se example:

example.jpg

Is there a way where I can Autogenerate the sequence of weeks, months and years from a start week/month including the formatting of the cells? The week is defined to belong to the month containing wednesday.

I was imagining some sort of popup that askes for the first week/month/year and the number of periods which then autogenerates the sequence..

I hope somebody can help me

[Richard Buttrey]

Hi,

Yes that should be fairly simple. Upload the workbook and tell us on what day Week1 starts in the first year i.e. perhaps 2014. The reason is this, you are showing weeks 1-52 but to make this workable across year boundaries you'll need to have a helper row (which may be hidden, but which contains the week number starting at the very first year. i.e. week numbers will continue 53,54,55 etc..

[nlegokloz]

Hi Richard

Thank you for looking into it

Here is the workbook.

gannt.xlsm

Regarding what day the first week starts in the first year I am not quite sure what you mean. The first year I need is 2015 as I am going to use it for new plans from now on. which day the first week starts on might not be to important as long as the week belongs to the month in which the wednesday is.

kind regards Nikolaj

[Richard Buttrey]

Hi,

Been a bit busy today and away for a few days tomorrow.

It struck me that there are dozens of free Gantt chart workbooks around the internet, I've used a few myself in the past as the basis for a bespoke development. So rather than re-invent the wheel maybe you can make use of this one.

The original is here http://davidseah.com/node/manual-gan...ting-in-excel/

The first sheet was just a manual layout where you would paint the cells yourself. I've added a start and end date for each task and a conditional format so that yellow bars will be painted in response to dates you enter.

Hope it helps