Formula to subtract values in one cell only...
 

sorry duplicate post.

Formula to subtract values in one cell only...
 

=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

Formula to subtract values in one cell only...
 

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

Formula to subtract values in one cell only...
 

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