Formula to subtract values in one cell only...

N

neilcarden

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
Top