Time Question

Time Question

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.

date: Sat, 05 Jul 2008 00:15:22 -0700 author: Paul

Re: Time Question

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Paul"  wrote in message 
news:7l7u64lph4kf49brn08p0eec8nug221b2d@4ax.com...
> 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.
>
>
>

date: Sat, 5 Jul 2008 09:48:24 +0100 author: Sandy Mann

Re: Time Question

Paul  wrote in news:7l7u64lph4kf49brn08p0eec8nug221b2d@
4ax.com:

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

date: Sat, 05 Jul 2008 08:47:20 GMT author: pub

Re: Time Question

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.

On Sat, 05 Jul 2008 00:15:22 -0700, Paul  wrote:

>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.  
>
-- 
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

date: Sat, 05 Jul 2008 10:27:35 +0100 author: Dave Mills

Re: Time Question

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 wrote:
> 
> 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.

-- 

Dave Peterson

date: Sat, 05 Jul 2008 06:41:01 -0500 author: Dave Peterson

Re: Time Question

"Paul"  wrote in message 
news:7l7u64lph4kf49brn08p0eec8nug221b2d@4ax.com...
> 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

date: Sat, 5 Jul 2008 21:00:30 +0100 author: Peter Andrews

Re: Time Question

Thanks to all.  It's working great now.


On Sat, 05 Jul 2008 08:47:20 GMT, pub  wrote:

>Paul  wrote in news:7l7u64lph4kf49brn08p0eec8nug221b2d@
>4ax.com:
>
>> 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.

date: Sat, 05 Jul 2008 23:29:29 -0700 author: Paul