Excel Project Templates Automating

[rabc_arch]

I have purchased the excel project templates and I have to say they are great.


I'm having a problem though.....


In the Gantt Chart Template


I want to automate the highlighting of the current week.

Also if I insert a column say 'Due Date' I would like the Start week to populate automatically using the ‘Due Date’.

So I need to calculate:

• What week # does the date fit into?

• Need to find the start week automatically


The only logical solution I can come up with is put in another data table which has a list of dates like below and do a vlookup.


Project Week Start 12/09/2011

Week Number

Monday 12/09/2011 1

Tuesday 13/09/2011 1

Wednesday 14/09/2011 1

Thursday 15/09/2011 1

Friday 16/09/2011 1

Saturday 17/09/2011 0

Sunday 18/09/2011 0

Monday 19/09/2011 2

Tuesday 20/09/2011 2

Wednesday 21/09/2011 2

Thursday 22/09/2011 2


I know this might not make sense but any help would be great.

 

[asa]

Hi rabc,


is the WEEKNUM() function what you're looking for?

WEEKNUM gives you the week number of the year


If you want to know the number of complete weeks between two dates, you could use:

INT(((end date)-(start date))/7)


Then there is the now largely undocumented DATEDIF function.  It will give you the count of periods between dates, but only supports year, month and day -- not week -- (with an additional possibiltiy to ignore elements of the dates during comparison):

=DATEDIF(date1,date2,period)

-- see http://office.microsoft.com/en-us/help/datedif-function-HA001160981.aspx


Asa

 

[NARAYANK991]

Hi rabc_arch ,


Just like to add to what @asa has already posted.


Since your week starts from Monday , given the project start date , do the following :


1. Find out the day of the week , of the project start date ; so if your project starts on 13/09/2011 , the day of the week will be Tuesday ( =WEEKDAY(A3) , assuming the project start date is in A3 ).


2. Find out the Monday of that week ( =A3-WEEKDAY(A3)+1 ).


3. Whatever are the dates , for which you wish to find out the week , e.g. if the date 19/10/2011 is in cell A4 , use the following formula :


=INT(((A4-($A$3-WEEKDAY($A$3)+1)-1)/7))+1


which will give you week 6.

 

[rabc_arch]

Thank you.


I will be using the formula from NARAYANK991


But it was a simple Hlookup that I trying and it returned the week that the date fell into that the item was due.


Thanks all for you comments