days calculation between dates

I would like to calculate the days based on three conditions. Here is the
example of my work sheet:
A                        B                        C             D
E           F
1    Start date           finish date           Jan          Feb        Mar
April
2    Jan 03, 2009        Jan 27, 2009
3    Feb 24, 2009
4    Feb 25, 2009       Mar 12, 2009
I want see the days caclulated by month for any task started. If task goes
to next month, days should go to next month column and I want to calculate
the weekdays only. As above row 3 date in column A is start date  of task and
task is not complete and we are in April. I want to see the days in for Feb
in D3 and days in Mar in E3. Once I show task complete, calculation should
end in April in F3.

I hope this is clear explanation. Thank you in advance for your help.

Kimti

Ah,I missed the weekdays only bit, that is much more difficult. let me think!!

Ah,

I missed the weekdays only bit, that is much more difficult. let me think!!

Mike

Hi,For this to work jan, Feb etc in row 1 must be properly formatted so

Hi,

For this to work jan, Feb etc in row 1 must be properly formatted so enter
1/1/2009 on C1 and format as mmm to show Jan and repeat for other months

Now put this formula in C2

=SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=C$1)*(ROW(INDIRECT($A2&":"&$B2))<DATE(YEAR(C$1),MONTH(C$1)+1,1)))

Drag down as required and then drag right as required. If one of the dates
is missing you will get a #REF! error and if this is not acceptable wrap the
entire formula in an iserror statement

Mike

Thanks for your help. It worked good on the cells have the dates.

Thanks for your help. It worked good on the cells have the dates. As you
stated if the one of the date is missing I got #REF!. But I do not know how do
I wrap the entire formula in an iserror statement. I will aslo wait for your
response on the weekday only.
Thanks,
Kimti

Hi,Still working on the workdays bit, this will get rid of the

Hi,

Still working on the workdays bit, this will get rid of the error

=IF(COUNT($A2:$B2)=2,SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=C$1)*(ROW(INDIRECT($A2&":"&$B2))<DATE(YEAR(C$1),MONTH(C$1)+1,1))),"")

Mike

Try this in c2, Drag down and right as

Try this in c2, Drag down and right as required

=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN(DATE(YEAR(C$1),MONTH(C$1)+1,0),$B2)))

Mike

Worked perfect with one exception.

Worked perfect with one exception. If B is blank, it is giving me numbers in
all the months based on date in A. Is there any way that formula can consider
B as current date unless there is actual date.
Thanks,
Kimti

Glad that worked,This now uses B2 or today's

Glad that worked,

This now uses B2 or today's date

=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN(DATE(YEAR(C$1),MONTH(C$1)+1,0),MAX($B2,TODAY()))))

If B2 is not empty and is less than today and C1 then you get incorrectresults.

If B2 is not empty and is less than today and C1 then you get incorrect
results.

This seems to work:

=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN(C$1+31-DAY(C$1+31),IF($B2="",NOW(),$B2))))

--
Biff
Microsoft Excel MVP

If you are expecitng the below results...

If you are expecitng the below results...try the below formula which uses
WEEKDAY()

Start date	finish date	1/1/2009	2/1/2009	3/1/2009
3-Jan-09	27-Jan-09	17	0	0
24-Feb-09		0	4	22
25-Feb-09	12-Mar-09	0	3	9


=MAX(0,SUM(INT((WEEKDAY(MAX(C$1,$A2)-{1,2,3,4,5},2)+MIN(IF($B2="",TODAY(),$B2),DATE(YEAR(C$1),MONTH(C$1)+1,0))-MAX(C$1,$A2))/7)))

If this post helps click Yes
---------------
Jacob Skaria

Just thought of something...

Just thought of something...

Since we are already using an ATP function**, NETWORKDAYS, we can replace
C$1+31-DAY(C$1+31) with the EOMONTH function. Saves a couple of keystrokes
and reduces the total calculations.

=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN(EOMONTH(C$1,0),IF($B2="",NOW(),$B2))))

** This formula requires the Analysis ToolPak add-in be installed for Excel
versions prior to Excel 2007.

--
Biff
Microsoft Excel MVP