Calculate the difference between two times - getting '#VALUE!' err

[Louis de Pointe du Lac]

I tried to use help, it gave me the same formula I was using, but when I try
to implement, I get the '#VALUE!' error.

Column A:
6/1/2007 9:30:51AM

Column B:
6/1/2007 9:33:08AM

Column C:
=SUM(B6-A6)

Office Help file:

1
2
A B
Start time End time
6/9/2007 10:35 AM 6/9/2007 3:30 PM
Formula Description (Result)
=B2-A2 Hours between two times with the cell formatted as "h" (4)
=B2-A2 Hours and minutes between two times with the cell formatted as "h:mm"
(4:55)
=B2-A2 Hours, minutes, and seconds between two times with the cell formatted
as "h:mm:ss" (4:55:00)
=TEXT(B2-A2,"h") Hours between two times (4)
=TEXT(B2-A2,"h:mm") Hours and minutes between two times (4:55)
=TEXT(B2-A2,"h:mm:ss") Hours, minutes, and seconds between two times
(4:55:00)



I've tried what the Help File says, but I still get the #VALUE! error.
If I hand type 9:30:51 in column A and 9:33:08 in column B, it works, but I
don't want to have to hand-type that, my queue system dumps a report with the
6/1/2007 9:30:51AM format, I want to be able to use that report.

Can anyone help?

 

[Gary''s Student]

Format all three cells as:

m/d/yyy [h]:mm:ss

and you should see:

6/1/2007 9:30:51 6/1/2007 9:33:08 1/0/1900 0:02:17


only the 2:17 is important here.

 

[bj]

It looks as though you actually have text and not a time value
if you use<format><cells><number> and change to general, does the cell change?
if not, it is text and you will have to convert it to a true time value.
Which version of excel are you using?

one way to generate a time value from separating the text into hours minutes
and seconds

hr =value(mid(date_time_text,find(":",date_time_tex)-2,2))
Min = value(mid(date_time_text,find(":",date_time_tex)+1,2))
Sec = value(left(right(date_time_text,4),2))
=time(hr,Min,Sec)

now the equations you referenced should work