Charge-transfer-like π→π* excitations in time-dependent density functional theory: a conundrum and its solution