Days calculation

[vaibhavgupte]

My query is. I m preparing a planning sheet

Column a Column B Column C Col D Col E Col F Col g

Start Date End date Duration Sep oct nov dec


15 sep 2010 14 nov 2010 =Colb-Cola my question is then how to calculate number of days in respective column D E F G for this range of dates.

 

[Luke M]

What exactly are you wanting in those columns? All your example says is that the column headers are month names. Do you need to know how many days out of the duration go into each month?

 

[Mike86]

If you make the headings of columns D-G into actual dates (Use 01mmm11 with a custom format of "mmm" to show only the three letter month text), then something like this would work:


=IF(AND(MONTH($A2)<=MONTH(D$1),MONTH($B2)>=MONTH(D$1)),MIN(DATE(YEAR(D$1),MONTH(D$1)+1,1)-1,$B2)-MAX(D$1,$A2),"")


With this method, as you extend the columns it will maintain the calculation.


If you want only working days, use the networkdays function:


=IF(AND(MONTH($A4)<=MONTH(D$1),MONTH($B4)>=MONTH(D$1)),NETWORKDAYS(MAX(D$1,$A4),MIN(DATE(YEAR(D$1),MONTH(D$1)+1,1)-1,$B4)),"")


Mike86

 

[vaibhavgupte]

Dear mike

I have prepared a schedule for a project which has starting date and completion date


A B C D E F G

Starting date Complation date Sep oct nov dec Jan

15 sep 2011 12 dec 2011 15 31 30 12 0


Now to calculate days as 15 days in sep, 31 days in oct as per column range A and B in respective month.


A excel formula is required in months column as my planning sheet extends upto 550 rows & to calculate for each row is tiresom.

 

[Luke M]

I believe this is what you're looking for:

=MAX(0,(D$1-C$1)-MAX(0,$A2+1-C$1)-MAX(0,D$1-$B2-1))


For this to work, as Mike suggested, your month headers need to be actual dates. You can change the formatting then to just display the month, but XL needs the actual date to do the comparisons.


Breakdown of formula:

First MAX is so we never have negative numbers. Simple enough.

Next, we first calculate how many potential days are in the month using the header rows. Then, we first subtract any days excluded using starting date. It appears you are excluding the start date in your example, hence the -1. Then, we subtract any days excluded from end month using completion date. Your example has an inclusive completion date, hence the +1.