Subtracting Dates to Find the Difference in Hours

  • Thread starter Thread starter Workbook
  • Start date Start date
W

Workbook

I am trying to create a formula that will find the difference in hours
between 1/9/2009 (I2) and 1/12/2009 (J2) in hours without including weekends.
Bob Phillips recommended this formula.

=NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6)
+(1-MOD(I2,1))*(WEEKDAY(I2,2)<6)
+(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6)

However when I try to use it I get this message #NAME? Any advice?
 
The NETWORKDAYS function requires the Analysis ToolPak add in. To activate
it, click Tools/Add-Ins on Excel's menu bar and put a check mark in its
CheckBox.
 
Hi Rick,

Thank you for the tip. I appreciate your help. The obstacle I am having
now is that the cell with the formula says True instead of giving the hours.
Do you have any thoughts what might be causing that? Changing the format of
the cells didn't seem to make a difference.
 
I can't duplicate your problem... the formula returns a numeric value for
me. Did you copy/paste the formula or, if you typed it, did you get all the
parentheses? Here is the formula in a form that I think will copy/paste
directly...

=NETWORKDAYS(I2,J2)-(WEEKDAY(I2,2)<6)+(1-MOD(I2,1))*(WEEKDAY(I2,2)<6)+(MOD(J2,1)-1)*(WEEKDAY(J2,2)<6)
 
I must have done something incorrectly with the formula. It works awesome
now. Thank you Rick, you're the man!
 
If you're getting TRUE rather than the numerical value, you have perhaps put
an = sign where there should have been a minus sign?

For your original question, if you having difficulty with an Excel function
your first port of call ought always to be Excel help for that function.
That would have told you the answer to your #NAME? problem.
 
Back
Top