Forum Discussion
Time formula error
I would be greatly appreciative of any assistance I could get.
I have a table in an excel document, which has numerous columns, two of which I enter a time into using the formula =TIME(hour,minute,second) now the first one works perfectly, no issues at all but the second column, (which is the first time rounded up to the nearest minute) always comes up with an error, no matter how many times I try to change it, fix it, delete it; it still doesn't seem to want to work for me.
I tried this outside of the table and I didn't have this problem, is there something I need to change in the table settings or is there something else I can do? Please help, it is frustrating me.
Thank you
1) Such alert is for the Tables only. Table assumes if the formula is used, that's the same formula for all cells of the column, and it's populated automatically with Table size changing. That's one of the main reasons why use Tables and not ranges. One column - one formula.
2) Excel "remembers" initial formula for the column and gives the alert if in some cells of the column formula differs from this initial one.
3) Thus that's Table-specific alert, you have no such outside the Table.
4) To fix - select the column and Del. Other words delete everything from it. Type any value, not formula, in any cell of the column (to avoid autofill). After that enter formulas into the cells, remove initial value.
It'll be no alert this time since there was no initial formula to "remember" for the column.
Please check in attached file.
8 Replies
there is no special case for table however if you have a sample file share to us may be we can get a problem if any there jenc1985
Hopefully this works and you can see the sample excel sheet that I have done. Khizar_Hayat
That's not an error, that's alert that you could use formulas in a wrong way. More about this here https://support.office.com/en-us/article/inconsistent-calculated-column-formula-5d2735ec-a8f2-4a06-a5f7-8b4352a4fa8d?ns=EXCEL&version=90&ui=en-US&rs=en-US&ad=US.
Most probably you copy/pasted formula from other cells and after that corrected them in place. If you delete cells values for entire column and start typing from scratch (as in attached) it shall be no such alert.
Or you may simply ignore it. In general, I see no reasons to use tables as it is used here, range will be more suitable.
