1904 negative time

D

Dale G

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).
 
D

Dave Peterson

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)
 
K

Kassie

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

--
HTH

Kassie

Replace xxx with hotmail
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top