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 |
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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 03:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com