Adding time Values up in Excel

J

John

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
 
B

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
 
J

John

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

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
 
Top