The setting only applies when you enter or edit a value. It doesn't convert values which are already stored in a cell.
Yeah... a slow burn that is difficult to notice until it's too late.
Excel is not designed to waste the time of frequent, experienced users while hand-holding casual users. It's designed to make power users very fast and accurate while possibly confusing casual and new users.
At the same time, we're clearly shooting ourselves in the foot by using Excel for this. This feature is just a hodge-podge solution to the problem of Excel not having strict data types. There should be enough cautionary tales (https://eusprig.org/research-info/horror-stories/) for everyone to know to avoid Excel.