It’s not terrible once you learn the muscle memory.
I’m sure there’s some obscure key bind to go directly there, but I just do Cmd+Shift+G in Finder to get to the browse to path dialog, type ~, and hit return.
Still a stupid extra amount of work, but at least it’s not having to use my mouse, typing %USERPROFILE%, or having to type the absolute path. That would piss me off even more.