Time formula error

[jenc1985]

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  

[#2]

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 

[#3]

Hopefully this works and you can see the sample excel sheet that I have done.  @Khizar_Hayat 

[#4]

@jenc1985 

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-a....

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.

[#5]

I agreed with the result of @Sergei backlan
if you want to remove an flag alert from the cell you can off it from the option > Formula > error checking (unselect) it will be off

[#6]

@Khizar_Hayat 

For inconsistent calculation alert just ignore will be very temporary solution - as soon as we re-enter any formula or enter new one this green triangle appears again. That's since very first we entered into the empty column was some formula. Excel fills entire column with such formula and keeps it internally. if continue with entering another formulas on the top we always will have inconsistent error.

It could be fully removed if only to avoid autofill at very beginning and Excel won't keep sample formula. We may undo fill on first step or start entering with value, not formula.

[#7]

That has been my issue, I "ignore this error" but it keeps coming back again as soon as I go to the next line. 

I haven't copied and pasted it from the previous column, I enter it in separately every time (I know there's probably a better way to do it having it automated or something but I'm still getting my head around everything)

I was playing around with it last night and I noticed that when I did it outside of the table it worked no problem, no issues at all with the flags or anything, that's why I was thinking it had something to do with the table. (I have attached another sample)

But I will definitely have a look at that link you sent through, thank you so much for that @Sergei Baklan 

[#8 · Accepted solution]

@jenc1985 

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.

[#9]

OMG thank you so much @Sergei Baklan 

It's worked, that is a huge relief. It's been frustrating me for quite some time and I never thought to sign up to a community like this, so thank you so much for you help.