In my experience the type of exercise matters a lot: after my average cycling training (50-100km) my legs are hurting but I feel more energetic and awake.
Mostly it results in staying up later than normal.
I think it works both ways simultaneously. I can run 1H30 in the afternoon, and go dancing until 4AM the same night, or go to bed at 10 and sleep like a baby.
Without exercise, I go to bed tired at 11 and can't sleep until 3 :)
