subtact between times

M

mreyes

I want to find the difference or elasped time between to times. my
spreadsheet is setup like this:
columnA columnB columnc
START END difference
TIME TIME
22.41.34 23.18.11 =(b1 - a1)

the cells are formattted as time but the value returnd in c1 is #VALUE!

Any suggestions are appreciated.
 
R

RagDyer

Change the decimals to colons.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
R

rahrah3a

if you change the data format to time using ":" to seperate
hours:minutes:seconds, this can be done

Format Cells - Number - Time - select 13:30:55
Or use Custom Format: h:mm:ss;@

results:
22:41:34 23:18:11 0:36:37
 
S

sirknightly

MrEyes,

Excel reads time as decimal values, so the multiple decimals are
throwing it off. Once you tell Excel how to read your data you'll be
fine. I'm assuming

part1.part2.part3

translates to

hour.minute.second

If so, use this formula:

=TIME(LEFT(B1,2),MID(B1,4,2),RIGHT(B1,2))-TIME(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))

Basically it takes the value in each cell, cuts it up into pieces, and
tells Excel that the first two characters are the HOUR, the two
characters in the middle are the MINUTE, and the right two characters
are the SECOND.

Knightly
 
Top