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

days calculation between dates
 

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

days calculation between dates
 

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

days calculation between 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 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

days calculation between dates
 

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

days calculation between dates
 

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

days calculation between dates
 

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

days calculation between dates
 

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

days calculation between dates
 

...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

days calculation between dates
 

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(YEA R(C$1),MONTH(C$1)+1,0))-MAX(C$1,$A2))/7)))

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


"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

days calculation between dates
 

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