Can anyone help me with a Formula

[Dick Smith]

Hi.
I'd appreciate it if some kind soul out there can work out a formula for me.
I've spent weeks trying to fathom one out but all I do is end up pulling my
hair out.
Ok I need a formula that will do the following:-

24hr clock times between and column C to calculate total hours

Column A Column B Column C
18.00 20.00 2
05.15 07.30 2.25
21.00 02.00 5
10.30 11.15 0.75
06.00 18.00 12
etc.etc.etc.

I'd appreciate any help that will help me solve the problem

Thanks in anticipation

Dick Smith

 

[Bob Phillips]

Hi Dick,

In C1

=MOD(B1-A1,1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Hi Dick,
Worksheet solution:
C2: =(B2-A2+(A2>B2))*24

There are 24 hours is a day. Time is a fraction of a day.
So a datetimestamp takes nor more space than either alone.

The B1>B2 is a logical it returns 0 if false and 1 if true.
Add 24 hours if A2>B2.

Of course you could the same type of logic in a program
(macro) if your really want to.

You could subtract them as times without multiplying
by 24 and format the result as time [h]:mm:ss
the [h] is more useful for totals and keeps hours
from overflowing into days.

More information on Date and Time at
http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[Guest]

Hi, I've done this before and will work on it but
basically what you need to do separate the hours and the
minutes by using left() and right() functions. You will
also need to test the minutes result and adjust your
calculation depending on whether the column B minutes
piece is less than that of column A.

John

 

[Bob Phillips]

Multiply my answer by 24 to get the style you wan t, that is decimal hours

=MOD(B1-A1,1)*24

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)