Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Dates and/or Times
Good afternoon,
I'm dealing with a modified =NETWORKDAYS formula but cannot seem to get it to total my results (especially when there is no start date/time and stop date/time entered into some of the cells in the columns I'm totaling). Is there a way to get the empty cells to be ignored and give me a correct total (SUM), see example below: Cell D13 = 10/13/2008 9:00:00 PM Cell F13 = 10/13/2008 9:30:00 PM =(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1) Cell N13 = 00:30 =SUM(N13:OFFSET(N21,-1,0)) Cell D14 = <blank Cell F14 = <blank =(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1) Cell N14 = ############## (indicating negative dates or times) I'm trying to total a list of these results from cell N13 through cell N21 by using the following formula: =SUM(N13:OFFSET(N21,-1,0)) When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect, it should equal 48:30. Cell formats are [h]:mm and my cell values are as follows: N13 = 00:30 N14 = 02:15 N15 = 02:00 N16 = ###### N17 = ###### N18 = ###### N19 = 43:45 N20 = ###### If anyone can assist please reply, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Dates and/or Times
Use
=IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1)) -- __________________________________ HTH Bob "watermt" wrote in message ... Good afternoon, I'm dealing with a modified =NETWORKDAYS formula but cannot seem to get it to total my results (especially when there is no start date/time and stop date/time entered into some of the cells in the columns I'm totaling). Is there a way to get the empty cells to be ignored and give me a correct total (SUM), see example below: Cell D13 = 10/13/2008 9:00:00 PM Cell F13 = 10/13/2008 9:30:00 PM =(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1) Cell N13 = 00:30 =SUM(N13:OFFSET(N21,-1,0)) Cell D14 = <blank Cell F14 = <blank =(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1) Cell N14 = ############## (indicating negative dates or times) I'm trying to total a list of these results from cell N13 through cell N21 by using the following formula: =SUM(N13:OFFSET(N21,-1,0)) When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect, it should equal 48:30. Cell formats are [h]:mm and my cell values are as follows: N13 = 00:30 N14 = 02:15 N15 = 02:00 N16 = ###### N17 = ###### N18 = ###### N19 = 43:45 N20 = ###### If anyone can assist please reply, Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Dates and/or Times
Bob - Thank you so much for the "correct" information on fixing my problem.
It worked perfectly and I certainly appreciate your time and effort in answering my question. "Bob Phillips" wrote: Use =IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1)) -- __________________________________ HTH Bob "watermt" wrote in message ... Good afternoon, I'm dealing with a modified =NETWORKDAYS formula but cannot seem to get it to total my results (especially when there is no start date/time and stop date/time entered into some of the cells in the columns I'm totaling). Is there a way to get the empty cells to be ignored and give me a correct total (SUM), see example below: Cell D13 = 10/13/2008 9:00:00 PM Cell F13 = 10/13/2008 9:30:00 PM =(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1) Cell N13 = 00:30 =SUM(N13:OFFSET(N21,-1,0)) Cell D14 = <blank Cell F14 = <blank =(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1) Cell N14 = ############## (indicating negative dates or times) I'm trying to total a list of these results from cell N13 through cell N21 by using the following formula: =SUM(N13:OFFSET(N21,-1,0)) When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect, it should equal 48:30. Cell formats are [h]:mm and my cell values are as follows: N13 = 00:30 N14 = 02:15 N15 = 02:00 N16 = ###### N17 = ###### N18 = ###### N19 = 43:45 N20 = ###### If anyone can assist please reply, Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Negative Dates and/or Times
Bob,
I'm back after I thought your solution corrected my previous problem and things were looking good, but something is wrong again with my negative dates and times. Here's my sample and function you provided: D153 E153 F153 N153 3/15/2009 17:20 Sunday 3/15/2009 18:00 Sunday ##### =IF(OR(D153="",F153=""),0,(NETWORKDAYS(D153,F153)-1)*("15:00"-"06:30")+MOD(F153,1)-MOD(D153,1)) Also, when totaling the elapsed times displayed in column N153 for all events how do I get that SUM to ignore blank cells or cells that display the ##### symbol? Thanks, Mike "Bob Phillips" wrote: Use =IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1)) -- __________________________________ HTH Bob "watermt" wrote in message ... Good afternoon, I'm dealing with a modified =NETWORKDAYS formula but cannot seem to get it to total my results (especially when there is no start date/time and stop date/time entered into some of the cells in the columns I'm totaling). Is there a way to get the empty cells to be ignored and give me a correct total (SUM), see example below: Cell D13 = 10/13/2008 9:00:00 PM Cell F13 = 10/13/2008 9:30:00 PM =(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1) Cell N13 = 00:30 =SUM(N13:OFFSET(N21,-1,0)) Cell D14 = <blank Cell F14 = <blank =(NETWORKDAYS(D14,F14)-1)*("15:00"-"06:30")+MOD(F14,1)-MOD(D14,1) Cell N14 = ############## (indicating negative dates or times) I'm trying to total a list of these results from cell N13 through cell N21 by using the following formula: =SUM(N13:OFFSET(N21,-1,0)) When I SUM all the cells (N13 through N20) I get 14:30 but that's incorrect, it should equal 48:30. Cell formats are [h]:mm and my cell values are as follows: N13 = 00:30 N14 = 02:15 N15 = 02:00 N16 = ###### N17 = ###### N18 = ###### N19 = 43:45 N20 = ###### If anyone can assist please reply, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
negative dates or times are displayed as # # # # | Excel Worksheet Functions | |||
negative dates or times are displayed as # # # # | New Users to Excel | |||
Negative times | Excel Discussion (Misc queries) | |||
negative times | Excel Worksheet Functions | |||
Negative times | Excel Discussion (Misc queries) |