days calculation between dates

K

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
 
M

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
 
K

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
 
M

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
 
M

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
 
K

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
 
M

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top