Time issues

[johnfli]

OK, here is a strange time problem.

I will try to make this as clear as I can as it is kind of confusing.
For columns, I,J,L,M,N,P and S the format is "Custum" with a property of
h:mm;@
Column R is set as currency.

In columns I,J,L,M the user enters in time in the 24 hr clock format.
(In this case, 23:58, 23:59, 00:05 and 00:11)

The formula in Column 'N' is =SUM(M12-I12)

If I enter in 1:58, 1:59, 2:05 and 2:11 (respectivly) column 'N' reads
0:13 as that is the time difference between M and I

With the times I first said, 'N' reads #############


ANy ideas on how I can fix this problem?

Thanks

 

[JE McGimpsey]

XL stores times as fractional days (e.g., 03:00 = 0.125). So if the
times span midnight, you need to add 1 to the "later" time. You can use
XL's ability to coerce boolean (TRUE/FALSE) values to 1/0, respectively.

=M12-I12+(M12<I12)

(Note: The SUM() in your original formula is unnecessary in XL)

A more obscure, but shorter, way to accomplish the same thing is

=MOD(M12-I12,1)

 

[Peo Sjoblom]

Try

=MOD(M12-I12,1)

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)