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
|