Time

[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

 

[Don Guillett]

Do the times include the date?

 

[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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

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

 

[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

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

 

[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

 

[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

 

[David McRitchie]

Hi Bill,
If you just have the time component and not the
date in cells and you can go through midnight you
would want to use
=(C1-B1 +(B1>C1))*24

B1>C1 is a logical expression returning either
true (value 1) for False (value 0), so it would
in effect add 24 hours to a negative hours result.

 

[Bill Ridgeway]

David,

Thanks for that. Someone on one of these NGs was asking how to do this just
a few days ago so I thought, as a challenge, I would try to resolve it.
It's an improvement on my effort. I hope the OP has also seen your reply
and taken note!

Regards.

Bill Ridgeway

 

[Roger Govier]

Hi Bill

In addition to David's answer, taking Sandy's method using MOD, will
also return the correct number of hours, if the times cross midnight.

=MOD(C1-B1,1) would return the result in hours in a format like 18:00
for a18 hours

=MOD(C1-B1,1)*24 will return 18 as a numeric value, with the cell
formatted as General or Number

Excel stores all times as fractions of a day, hence the need to multiply
by 24, if you want a numeric as opposed to a time result.

 

[Bill Ridgeway]

Thanks Roger. Is there a practical difference between-
=(C1-B1+(B1>C1))*24
and
=MOD(C1-B1,1)*24

Thanks.

Bill Ridgeway

 

[Roger Govier]

Hi Bill

Less keystrokes!!<bg>
They perform exactly the same function.

 

[Sandy Mann]

Bill,

There is no real difference between the two formulas. =(C1-B1+(B1>C1))*24
is more intuitive - once you know that the TRUE or FALSE of the (B1>C1) is
coerced into a 1 or 0 respectively by the addition - and of course
=MOD(C1-B1,1)*24 is slightly less typing <g>


--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk