calculate difference between two times

[Guest]

I have tried many different formats and formulas and the worksheet will not
calculate the difference between two times formatted as follows.

6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE!
B8 C8

the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!!

I have read the Excel help and it did not help.

 

[Guest]

hi Ken, is it formated as text?

i have been tryed id here and works =00:05:41

hth
regards from Brazil
Marcelo

"ken" escreveu:

 

[Biff]

Check and make sure that the entries are in fact real Excel dates/times.

Try these tests:

=ISNUMBER(B8)
=ISNUMBER(C8)

Both should return TRUE.

If either or both return FALSE....

Select either B8 or C8
Goto the menu Data>Text to Columns
Select Delimited
Click Finish

Repeat for the other cell if needed.

That might convert the entries to real dates/times.

Biff

 

[Guest]

Hi Marcel and Biff
i have tried both of your suggestions and still I get #VALUE!.

i have tried typing in the time manually and still nothing.

i am running out of ideas.

 

[Guest]

Ken,

try to format the B8 as number it should return 38880,8355787037
do the same to C8 will be 38880,839525463

si this works ?

regards


"ken" escreveu:

 

[Guest]

I think that I have finally figured it out. When I manually enter the data
it works. I will have to write a macro or something to convert from string
to date format.

 

[David Biddulph]

I think that I have finally figured it out. When I manually enter the data
it works. I will have to write a macro or something to convert from
string
to date format.

You shouldn't need a macro. After you've formatted the cells as dates, put
a zero in a spare cell, copy, select the cells you want turning to dates,
then paste special/ add.