Time Question

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Column D: 11:01 PM
Column E: 11:10 PM

Using this formula =IF(C116="","",IF(D116="",0,D116-C116)) I get in
column C: 0:09 (Elapsed minutes, which is what I want.)

All well and good until this happens

D: 11:01 pm
E: 12:03 am

Not I get gobbledeegook.
 
Column D: 11:01 PM
Column E: 11:10 PM

Using this formula =IF(C116="","",IF(D116="",0,D116-C116)) I get in
column C: 0:09 (Elapsed minutes, which is what I want.)

All well and good until this happens

D: 11:01 pm
E: 12:03 am

Not I get gobbledeegook.
Click to expand...

the problem in c116 is that you are getting negative time. so you get
the ###### signs.
do you want c116 to show 1 hour 2 minutes? or 22:58 minutes?

for 1:02 use this
=E116-D116+(E116<D116)

for 22:58 use this
=(MAX(D116:E116)-MIN(D116:E116))

hope thats what you are looking for.
 
I assume that you have typos in your formula but with the start time (11:01
PM) in D116 and the end time (12:03 AM) in E116 try:

=IF(COUNT(D115:E115)<>2,"",MOD(E115-D115,1))

to give 01:02

Can be used for any time of day not just crossing midnight.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Your formula seem wrong as C116-D116 would ref itself. Assuming you meant to use
D & E as the source data. Then the time diff become negative in the second case
which is invalid. Format C116 as a number to see this.
 
Maybe it would be a good idea to include the date with the time if there's a
possibility that you're changing dates.

And if you include the date, you won't have to worry if/when you cross two or
more midnights.
 
Paul said:
Column D: 11:01 PM
Column E: 11:10 PM

Using this formula =IF(C116="","",IF(D116="",0,D116-C116)) I get in
column C: 0:09 (Elapsed minutes, which is what I want.)

All well and good until this happens

D: 11:01 pm
E: 12:03 am

Not I get gobbledeegook.
Click to expand...

Shouldn't 12:03am be 00:03, i.e. three minutes past midnight. But you will
need to include a date if doing any calculations passing through midnight.

Peter
 
Thanks to all. It's working great now.


the problem in c116 is that you are getting negative time. so you get
the ###### signs.
do you want c116 to show 1 hour 2 minutes? or 22:58 minutes?

for 1:02 use this
=E116-D116+(E116<D116)

for 22:58 use this
=(MAX(D116:E116)-MIN(D116:E116))

hope thats what you are looking for.
Click to expand...
 
Back
Top