Adding time Values up in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 13 Sep 2005 13:36 GMT

Im working on a Spreadsheet and i need the formula to work out the hours
worked.
The format of the way that the data is recived is for example 12:00-20:00 so
i need to to tell me that there has been 8 hours worked.

Could anyone help us with this

thanks

Reply to this Message

Bob Phillips - 13 Sep 2005 13:49 GMT

If you want the result as time, use

=(MID(A11,FIND("-",A11)+1,99))+0-LEFT(A11,FIND("-",A11)-1)

If decimal hours, use

=(MID(A11,FIND("-",A11)+1,99))*24-(LEFT(A11,FIND("-",A11)-1))*24

Signature

HTH

Bob Phillips

> Im working on a Spreadsheet and i need the formula to work out the hours
> worked.
[quoted text clipped - 4 lines]
>
> thanks

Reply to this Message

John - 13 Sep 2005 14:08 GMT

I have tried that but with no sucsess this is the way in witch the data is
set out .

08:30-21:00
08:30-17:30
11:00-19:00
10:00-18:00
14:00-18:00
19:00-24:00
11:00-19:00
09:30-17:30
10:30-18:30
08:30-17:00

I need it to total the hours worked i tried a sum formula but it wont add up
the total hours worked.

> If you want the result as time, use
>
[quoted text clipped - 13 lines]
> >
> > thanks

Reply to this Message

Bob Phillips - 13 Sep 2005 14:46 GMT

I have just tried it with your data and it works fine.

In which way no success? If it was that the time sum came out as 7 hours,
form at that cell as [hh]:mm.

You can also sum it in one formula

=SUMPRODUCT(--((MID(A1:A10,FIND("-",A1:A10)+1,99))+0-LEFT(A1:A10,FIND("-",A1

:A10)-1)))

Signature

HTH

Bob Phillips

> I have tried that but with no sucsess this is the way in witch the data is
> set out .
[quoted text clipped - 30 lines]
> > >
> > > thanks

Reply to this Message

Roger Govier - 13 Sep 2005 14:55 GMT

Hi John

Bob's solution works fine for me.
You need to format your total cell as Format>Cells>Number>Custom> [hh]:mm
in order to get it to roll over past 24 hours.

As an alternative to the formula method, you could mark your block of data
and choose Data>Text to Columns>Delimited> check the Other box and insert
the symbol - and click finish.

If the original data was in column A, your start times will be in A and your
finish times will be in B. In column C enter
=B1-A1
Sum the range of C setting the format of the summation cell to [hh]:mm as
described above.

Regards

Roger Govier

> I have tried that but with no sucsess this is the way in witch the data is
> set out .
[quoted text clipped - 32 lines]
>>>
>>>thanks

Reply to this Message