days calculation between dates

  • Thread starter Thread starter Kimti
  • Start date Start date
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
 
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
 
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
 
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 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. 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 date

=MAX(0,NETWORKDAYS(MAX(C$1,$A2),MIN(DATE(YEAR(C$1),MONTH(C$1)+1,0),MAX($B2,TODAY()))))
 
...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))))
 
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
 
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.
 
Back
Top