Finding Time difference

J

John Harris

I know this has to be easy. I have a start time and an end time. I need the
difference in hours and minutes. I used the examples in the help, but it
converts it to text and then it won't add. Anyone?

TIA

JC
 
J

JE McGimpsey

One way:

A1: <start time>
B1: <end time>

C1: =B1-A1

format C1 as time.

XL stores times as fractional days (e.g., 3:00 = 0.125), so if your
times span midnight, the "later" time will be smaller than the "earlier"
time. Compensate that by adding 1 to the "end time". You can take
advantage of XL's automatic coercion of boolean TRUE/FALSE values to 1/0:

=(B1<A1) + B1 - A1

or the more obscure, but equivalent:

=MOD(B1-A1, 1)
 
D

Dave Peterson

One more version that might be even more clear:

=B1-A1+IF(B1<A1,1,0)

Still following JE's rules:
A1: <start time>
B1: <end time>
 
Top