Negative Dates and/or Times

[watermt]

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

 

[Bob Phillips]

Use

=IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1))

 

[watermt]

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.

Use

=IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1))

--
__________________________________
HTH

Bob

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

 

[watermt]

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

Use

=IF(OR(D13="",F13=""),0,(NETWORKDAYS(D13,F13)-1)*("15:00"-"06:30")+MOD(F13,1)-MOD(D13,1))

--
__________________________________
HTH

Bob

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