The rotation of planets hosting atmospheric tides: from Venus to habitable super-Earths