sorry duplicate post.

On Wed, 1 Nov 2006 12:14:59 +0000, neilcarden
wrote:


Hi

If I have a cell that contains '09:00-17:30' - is there a formula or
way I can get the amount of hours worked into another cell without
using text to columns to seperate the values and work it out that way?

Thanks in advance
Neil

If the format is exactly as you describe, then:

=LEFT(RIGHT(A1,6),5)-RIGHT(LEFT(A1,6),5)

Format the result as h:mm

OR, if you want just the number of hours, so as to be useful in a salary
calculation, use:

=24*(LEFT(RIGHT(A1,6),5)-RIGHT(LEFT(A1,6),5))

and format as General or as Number with the required number of decimal places.


--ron

On Wed, 01 Nov 2006 08:02:22 -0500, Ron Rosenfeld
wrote:

On Wed, 1 Nov 2006 12:14:59 +0000, neilcarden
wrote:


Hi

If I have a cell that contains '09:00-17:30' - is there a formula or
way I can get the amount of hours worked into another cell without
using text to columns to seperate the values and work it out that way?

Thanks in advance
Neil

If the format is exactly as you describe, then:

=LEFT(RIGHT(A1,6),5)-RIGHT(LEFT(A1,6),5)

Format the result as h:mm

OR, if you want just the number of hours, so as to be useful in a salary
calculation, use:

=24*(LEFT(RIGHT(A1,6),5)-RIGHT(LEFT(A1,6),5))

and format as General or as Number with the required number of decimal places.


--ron


The basic formula could be written also as:

=MID(A1,8,5)-MID(A1,2,5) or

=24*(MID(A1,8,5)-MID(A1,2,5))


--ron

Thanks guys!!! all are great...

Save's me a days work now!!!

Cheers

=MID(A1,8,5)-MID(A1,2,5)
Format as [h]:mm
--
David Biddulph

"neilcarden" wrote in message
...

Hi

If I have a cell that contains '09:00-17:30' - is there a formula or
way I can get the amount of hours worked into another cell without
using text to columns to seperate the values and work it out that way?

Thanks in advance
Neil