Hi magikheart!
I have an extensive background in fitness/sports, so hopefully I can help shed a bit of insight here. Working out immediately before bed actually is going to have the opposite of your desired effect (as you already know!) because of the endorphins flowing through your body. Exercise creates an endorphin rush, which leaves you feeling energized and not in an idea position for sleep. When it comes down to it, there’s not a single “sweet spot,” per say, that’s going to help you sleep better- ultimately, it’s about finding what works best for you. Personally, my ideal time to work out is around 4/5 hours before I go to sleep- this leaves me with plenty of time to wind down, relax, and get ready for bed. I’ve also had success with exercise 2/3 hours before bed. I know of colleagues who swear by exercising first thing in the morning, because it wakes you up and gets you moving for the day, and then your evening will be much more relaxing. However, as I said, this is all about trial and error! I hope this helped you!