Time Question

[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.

 

[pub]

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.

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.

 

[Sandy Mann]

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

 

[Dave Mills]

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.

 

[Dave Peterson]

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.

 

[Peter Andrews]

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.

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

 

[Paul]

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.