# 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

Ah,

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

Mike

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

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.