1904 negative time
 

I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

1904 negative time
 

You do it this way:
=if(d1="","",d1-c1)

Regards,
Fred.

"Dale G" wrote in message
...
I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a
blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I
enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

1904 negative time
 

Times (and dates) are just numbers to excel. So you could modify your formula
to check to see how many numbers are in those cells before you do anything...

=if(count(c2:d2)<2,"",d2-c2)
(formatted nicely)

If you want to treat negative times as exactly on time (no credit for arriving
early), you could use something like this to determine that adjusted average:

=sumif(e:e,""&0,e:e) / count(e:e)
or
=sumif(e:e,""&0) / count(e:e)




Dale G wrote:

I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

--

Dave Peterson

1904 negative time
 

Let's sa your data starts in row 2. Then, in E2, enter =IF(D2="","",D2-C2)

--
HTH

Kassie

Replace xxx with hotmail


"Dale G" wrote:

I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

1904 negative time
 

Something like:

=IF(OR(D1="",C1=""),"",D1-C1)
--
Gary''s Student - gsnu200909


"Dale G" wrote:

I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

1904 negative time
 

Excellent, thanks for all the help. I think I'll use =IF(D3="","",D3-C3).

"Dave Peterson" wrote:

Times (and dates) are just numbers to excel. So you could modify your formula
to check to see how many numbers are in those cells before you do anything...

=if(count(c2:d2)<2,"",d2-c2)
(formatted nicely)

If you want to treat negative times as exactly on time (no credit for arriving
early), you could use something like this to determine that adjusted average:

=sumif(e:e,""&0,e:e) / count(e:e)
or
=sumif(e:e,""&0) / count(e:e)




Dale G wrote:

I would like to use the 1904 date system to calculate positive & negative
time. I have column (c) preset with an scheduled arrival time, and a blank
column (D) to enter the actual arrivel time. Column E is set with =D-C for
the difference.
Column E will always show the negative time of C (e.g -4:50) until I enter
the actual arrived time in D. Is there a way to have column E remain blank
until I enter the actual time. (Also the negative time in E effects other
calulations like Average).

--

Dave Peterson
.