| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WORK HOURS DIFFERENCE BETWEEN TWO DATES | Excel Worksheet Functions | |||
| Subtracting first and last values in a row to find net difference | Excel Worksheet Functions | |||
| get hours between 2 dates subtracting the weekends | Excel Discussion (Misc queries) | |||
| Need difference between two dates/times in hours | Excel Worksheet Functions | |||
| Subtracting dates to get hours... but I want to skip weekends | Excel Discussion (Misc queries) | |||
Linear Mode
