Just thought of something...

Since we're 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


"T. Valko" wrote in message
...
...MAX($B2,TODAY()))))

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


"Mike H" wrote in message
...
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()))))

"Kimti" wrote:

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

"Mike H" wrote:

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

"Mike H" wrote:

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(Y EAR(C$1),MONTH(C$1)+1,1))),"")

Mike

"Kimti" wrote:

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 don't
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

"Mike H" wrote:

Ah,

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

Mike

"Mike H" wrote:

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'll get a #REF! error and if this is not
acceptable wrap the
entire formula in an iserror statement

Mike



"Kimti" wrote:

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