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
"Louis de Pointe du Lac" wrote:
> 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?
|
Similar Threads
