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.

Paul <Msr33@TPx12.com> 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.

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" <Msr33@TPx12.com> 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.
>
>
>

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 <Msr33@TPx12.com> 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.

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

"Paul" <Msr33@TPx12.com> 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

Thanks to all. It's working great now.


On Sat, 05 Jul 2008 08:47:20 GMT, pub <puiblicstuff@home.com> wrote:

>Paul <Msr33@TPx12.com> 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.