Adding time Values up in Excel
 

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

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

--
HTH

Bob Phillips

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

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" wrote:

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

--
HTH

Bob Phillips

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

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

--
HTH

Bob Phillips

"John" wrote in message
...
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" wrote:

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

--
HTH

Bob Phillips

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

Hi John

Bob's solution works fine for me.
You need to format your total cell as FormatCellsNumberCustom [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 DataText to ColumnsDelimited 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


John wrote:
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" wrote:


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

--
HTH

Bob Phillips

"John" wrote in message
...

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