Adding time Values up in Excel

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

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.

 

[Bob Phillips]

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)))

 

[Roger Govier]

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