Subtracting Dates to Find the Difference in Hours
 

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?

Subtracting Dates to Find the Difference in Hours
 

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?

Subtracting Dates to Find the Difference in Hours
 

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.


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?

Subtracting Dates to Find the Difference in Hours
 

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)

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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.


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?

Subtracting Dates to Find the Difference in Hours
 

I must have done something incorrectly with the formula. It works awesome
now. Thank you Rick, you're the man!

"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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.


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?

Subtracting Dates to Find the Difference in Hours
 

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.
--
David Biddulph


"Workbook" wrote in message
...
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.


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?

Subtracting Dates to Find the Difference in Hours
 

thanks for the input David, I appreciate your feedback.

Ed

"David Biddulph" wrote:

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.
--
David Biddulph


"Workbook" wrote in message
...
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.


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Workbook" wrote in message
...
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?