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

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)

"Dick Smith" wrote in message
...
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

Hi Dick,
Worksheet solution:
C2: =(B2-A2+(A2B2))*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 B1B2 is a logical it returns 0 if false and 1 if true.
Add 24 hours if A2B2.

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dick Smith" wrote in message ...
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

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
-----Original Message-----
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






.

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)

"Bob Phillips" wrote in message
...
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)

"Dick Smith" wrote in message
...
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