On my machine, because of the way I have things set up, doing:
cd ~ # /work1/jleffler
cd ~jleffler # /u/jleffler
The first pays attention to the value of environment variable $HOME; I deliberately set my $HOME to a local file system instead of an NFS-mounted file system. The second reads from the password file (approximately; NIS complicates things a bit) and finds that the password file says my home directory is /u/jleffler and changes to that directory.
The annoying stuff is that most software behaves as above (and the POSIX specification for the shell requires this behaviour). I use some software (and I don't have much choice about using it) that treats the information from the password file as the current value of $HOME, which is wrong.
Applying this to the question - as others have pointed out, 'cd ~x' goes to the home directory of user 'x', and more generally, whenever tilde expansion is done, ~x means the home directory of user 'x' (and it is an error if user 'x' does not exist).
It might be worth mentioning that:
cd ~- # Change to previous directory ($OLDPWD)
cd ~+ # Change to current directory ($PWD)
I can't immediately find a use for '~+', unless you do some weird stuff with moving symlinks in the path leading to the current directory.
You can also do:
cd -
That means the same as ~-.