days calculation between dates

[Kimti]

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

 

[Mike H]

Ah,

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

Mike

 

[Mike H]

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]

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]

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

 

[Mike H]

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

 

[Kimti]

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]

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()))))

 

[T. Valko]

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

 

[Jacob Skaria]

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

 

[T. Valko]

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.