Difference between two times results in #VALUE

[fdv]

Version 2007
This is important: the fields in question are formatted as times.
I have two times:
cell A1 is 14:53:27
and
cell B1 is 14:53:30

So I entered in C1 this: =B1-A1 and got #VALUE

I tried searching online and found Microsoft telling me to do the same thing.
What I would like is to see a returned value of 3 seconds, like 00:00:03 or something. I tried several variants I found on the MS site but only get #VALUE

It gets better.

So I tried entering 1 in one cell, and 1 in another, formatted them as numbers, and entered =sum(A3:A4) in yet a third, hit enter, and the math doesn't occur. I mean, the cell is sitting there with =sum(A3:A4) in it and doesn't add 1 and 1 to get 2.

Next? Yeah, you guessed it. I typed 1 into a cell, D5, formatted it as a number, clicked on another cell, hit = and clicked on the 1, and it did not come up as 1, it sat there as "=D5"

Excel is a neat program and all, but after I'm done with my project I think I might try to find something that's... more capable I guess is the best way to put it. Previous versions actually did the math rather than timebombing (this isn't even a trial, this is a purchased, registered copy).

 

[repairman615]

Hello,

This could be due to 'Show Formula' on the Formulas tab in the ribbon.

Is this by chance selected?

 

[fdv]

"Show formulas in their cells instead of calculated results" is not checked.
Sorry I did not state that

 

[repairman615]

There are two places I know of that do basically that. One is in the excel options on the advanced tab, Your verbage is identical to this. Also there is one other place to look. On the ribbon in 2007 (I am using this version)find the formulas tab. There is an option there 'Show formulas'. Is this one ok too?

I am still trying to get a #value error by subtracting times...


-Jeff

 

[fdv]

Hi Jeff thanks for helping. Here is a pic of this screen

 

[repairman615]

No problem, your may think I am a badger here... the screenshot is from the Excel Options using the Orb / Office button (Where the File Drop down previously was). The Ribbon is along the top of your screen, try these keystrokes:

CTRL+`

Hold down CTRL and press the key to the left of the number 1


This will toggle what I am thinking could be it. I could be wrong, but I hope not.

 

[fdv]

Wow, I get logged out after EVERY post. Now that is security.

CTRL and ` seems to toggle show formula, and now I seem to have some basic arithmetic back.
Edit: Nope, still displaying a formula despite CTRL and ` and despite selecting the option manually on the ribbon.

Still no luck on times.

 

[repairman615]

Glad you got the results back. When the show formula is ticked, the results are still there, we just cant see them.

On to the Value err.

Check if cell B1has a space in the beginning... or one of the cells contains a space where it shouldn't.

 

[fdv]

Check if cell B1has a space in the beginning... or one of the cells contains a space where it shouldn't.

Bing - f?ing - go.

Dude if you lived next door I'd give you a sixpack.

Thanks a mil on this, one column of the fields has a space in front of EVERY value, and the other column does not. Very hard to see with Calibri. Teaches me to use a fixed width font next time.

 

[repairman615]

... you time subtractors kill me.

I am glad this was panned out.

Welcome to the Board BTW!!

-Jeff