Android - Does the alarm work when my phone is turned off?

No, if the phone is off, it can't do anything. If it's in sleep mode where the screen is off and it's not in use then the alarm will still function as will other types of notifications. I have never tried this specifically on a Galaxy S but I am fairly confident this is a universal rule.

Unfortunately, it doesn't work when phone is powered off as some have already suggested.

My suggestion: put it into Airplane Mode - this way, the phone almost consumes zero energy (due to not having any network connection and so on), and you have a good chance that the battery will last until the morning where the alarm shall go off!

No

Tested it. It didn't go off when the phone was off.