Tilde expansion is required by POSIX (see that first linked page) and appears in all modern Bourne-style shells. That includes the popular shells bash, ksh93, and zsh, but also more minimalist shells like mksh, dash, and busybox ash.
In practice, different POSIX-compatible shells sometimes differ in the precise details of tilde expansion, both in the unspecified case that HOME would be used but is unset or empty, and to permit ~ notation to be used for other purposes than expanding users' home directories. For example, tilde expansion in bash also provides a shorthand for accessing the values of the PWD and OLDPWD variables, with ~- and ~+, respectively.
However, in the typical cases, it works about the same across Bourne-style shells. These are typical cases (but note that this way of separating them is not official, it's just my way of presenting the material):
~ or ~/ by itself expands to your home directory.
~/ followed by more path components expands to a path starting at your home directory.
~username or ~username/ by itself expands to the home directory of the user whose username is username.
~username/ followed by more path components expands to a path starting at the home directory of the user whose username is username.