"It is hot in the afternoon."
Surprised no one has answered this yet, but I'll give it a shot.
The English translation is always the best possible translation. This goes without saying, but rather than translating literally, often it is more like what would be the normal way to word the sentence in English. It is phrased here as "in," the afternoon, but [で] does not literally translate to that. As you pointed out, the English translation isn't "The afternoon is hot," because whilst that would be correct and a more direct translation, that's still an uncommon way of wording it.
You have to FEEL the meaning of Japanese words and particles a bit more than think about what they LITERALLY mean. It's safe think of [で] in your head as meaning "with which," or "via," whatever. Then you can see for yourself that using [で] would not be correct. Hopefully my explanation isn't counterproductive and somebody with a similar question finds this at least somewhat useful lmao.
Just in case, this: https://www.tofugu.com/japanese/japanese-particles-cheatsheet/ is a great help for all particles.