Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Serena
 
Posts: n/a
Default Excel 2003 - Formula result shows as 0:00

I have a problem occurring on a number of spreadsheets that I use regularly.
For some reason, the formula results are not updating correctly and are
showing the result as 0:00. All columns are formatted as [h]:mm.

Formula as shown =IF(C12<"",+C11-C12,"") appears in D12. This is the same
formula as rows above but rows above show correct result whereas D12 and
subsequent rows show only zero.

If I do an F2 and F9, the correct result shows in the Formula Editing bar so
formula is working correctly. I have tried copying both formula and cell
formatting from previous rows which do display their result correctly but
still doesn't fix the problem.

Options are set to Automatic Recalculation, doing extra recalcs has no
effect. One affected spreadsheet seems to have fixed itself when I closed
and reopened it.

Any assistance would be much appreciated

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
check that the cell is not formated as 'Text'. If it is: Change the
format to 'General (or your custom format) and re-enter the formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Serena" schrieb im Newsbeitrag
...
I have a problem occurring on a number of spreadsheets that I use

regularly.
For some reason, the formula results are not updating correctly and

are
showing the result as 0:00. All columns are formatted as [h]:mm.

Formula as shown =IF(C12<"",+C11-C12,"") appears in D12. This is

the same
formula as rows above but rows above show correct result whereas D12

and
subsequent rows show only zero.

If I do an F2 and F9, the correct result shows in the Formula Editing

bar so
formula is working correctly. I have tried copying both formula and

cell
formatting from previous rows which do display their result correctly

but
still doesn't fix the problem.

Options are set to Automatic Recalculation, doing extra recalcs has

no
effect. One affected spreadsheet seems to have fixed itself when I

closed
and reopened it.

Any assistance would be much appreciated


  #3   Report Post  
Serena
 
Posts: n/a
Default

All cells involved in the formula are formatted as [h]:mm

"Frank Kabel" wrote:

Hi
check that the cell is not formated as 'Text'. If it is: Change the
format to 'General (or your custom format) and re-enter the formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Serena" schrieb im Newsbeitrag
...
I have a problem occurring on a number of spreadsheets that I use

regularly.
For some reason, the formula results are not updating correctly and

are
showing the result as 0:00. All columns are formatted as [h]:mm.

Formula as shown =IF(C12<"",+C11-C12,"") appears in D12. This is

the same
formula as rows above but rows above show correct result whereas D12

and
subsequent rows show only zero.

If I do an F2 and F9, the correct result shows in the Formula Editing

bar so
formula is working correctly. I have tried copying both formula and

cell
formatting from previous rows which do display their result correctly

but
still doesn't fix the problem.

Options are set to Automatic Recalculation, doing extra recalcs has

no
effect. One affected spreadsheet seems to have fixed itself when I

closed
and reopened it.

Any assistance would be much appreciated



  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
what is in C11:C12?

--
Regards
Frank Kabel
Frankfurt, Germany

"Serena" schrieb im Newsbeitrag
...
All cells involved in the formula are formatted as [h]:mm

"Frank Kabel" wrote:

Hi
check that the cell is not formated as 'Text'. If it is: Change the
format to 'General (or your custom format) and re-enter the formula

--
Regards
Frank Kabel
Frankfurt, Germany

"Serena" schrieb im Newsbeitrag
...
I have a problem occurring on a number of spreadsheets that I use

regularly.
For some reason, the formula results are not updating correctly

and
are
showing the result as 0:00. All columns are formatted as [h]:mm.

Formula as shown =IF(C12<"",+C11-C12,"") appears in D12. This

is
the same
formula as rows above but rows above show correct result whereas

D12
and
subsequent rows show only zero.

If I do an F2 and F9, the correct result shows in the Formula

Editing
bar so
formula is working correctly. I have tried copying both formula

and
cell
formatting from previous rows which do display their result

correctly
but
still doesn't fix the problem.

Options are set to Automatic Recalculation, doing extra recalcs

has
no
effect. One affected spreadsheet seems to have fixed itself when

I
closed
and reopened it.

Any assistance would be much appreciated




  #5   Report Post  
Serena
 
Posts: n/a
Default

Can't remember which sheet I took that example from but details for one of
the sheets are as follows :-

C89 = 5:30:00 a.m. which displays as 5:30 because of the [h]:mm
D89 = 6:51:00 a.m. which displays as 6:51
E89 = =IF(C89 <"",IF(D89 <"",+D89-C89,"no finish time"),""), which gives a
result if I F2, F9 of 0.05625 but displays as 0:00

I've tried reformatting and recalcing E89 in various ways, but all give a
display of 0 in various forms.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
In Excel 2003, the histograms are incorrect. Excel shows a bar gr. Levi Excel Discussion (Misc queries) 2 January 25th 05 04:17 AM
sharing/using/saving Excel 2002 files in Excel 2003 maze2009 Excel Discussion (Misc queries) 0 January 20th 05 08:27 PM
Excel 2003 - cells in formula? Tom Walker Excel Discussion (Misc queries) 1 December 24th 04 02:14 AM
How do I format a character from a result of formula in excel? Mohamed Excel Discussion (Misc queries) 2 December 9th 04 10:43 AM


All times are GMT +1. The time now is 03:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"