Time

B

Bill Ridgeway

I want to calculate elapsed time. Column A has a start time. Column B has
an end time (which may be the following day). I want a formula for column C
which will give the elapsed time. The formula =B1-A1 doesn't work

Thanks.

Bill Ridgeway
 
S

Sandy Mann

If it is not going to be more than 24 hours then :

=MOD(B1-A1,1) should return the correct answer.

If it could be more than 24 hours then you will have to include the dates

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
D

Dave Peterson

=b1-a1+(a1>b1)

Will add 24 hours if the start time is later than the end time (start at 2PM and
finish at 1AM)

But if you include the date, you'll never have to assume that the times are on
consecutive days.
 
K

K1KKKA

I want to calculate elapsed time. Column A has a start time. Column B has
an end time (which may be the following day). I want a formula for column C
which will give the elapsed time. The formula =B1-A1 doesn't work

Thanks.

Bill Ridgeway
Click to expand...

Bill,

I Tend to use the following to some success

=SUM(A1-INT(A1))/0.6+INT(A1) <-- Put in Column C
=SUM(B1-INT(B1))/0.6+INT(B1) <-- Put in Column D
=IF(C1>D1,D1-C1+24,D1-C1) <-- Put in Column E
=0.6*(E1-INT(E1))+INT(E1) <-- Put in Column F


Then copy down to all relevant cells, like i said, maybe not the best
way, but it works for me.

Need to use the following format instead of the usual time format
00.00



Steve
 
B

Bill Ridgeway

Thanks Steve for your suggestion which I thought rather cumbersome and, for
me at least, didn't fit the bill in being able to total the elapsed time.
By trial and error I have come up with another solutions which seems to work
in the scenario I describe - although I haven't tested it thoroughly. The
formula in Column C is =(C1-B1)*24

Regards.

Bill Ridgeway
Computer Solutions
 
B

Bill Ridgeway

Thanks Steve for your suggestion which I thought rather cumbersome and, for
me at least, didn't fit the bill in being able to total the elapsed time.
By trial and error I have come up with another solutions which seems to work
in the scenario I describe - although I haven't tested it thoroughly. The
formula in Column C is =(C1-B1)*24

Regards.

Bill Ridgeway
Computer Solutions
 
Top