Basically, there seem to be three different behaviours of "Remember Me" authentication:
Definite: After a successful login, a user doesn't have to provide their credentials during a time span with a definite end (e.g. after 7-14 days). Recurring visits do not extend the period. E.g. Google seems to use this strategy and asks for re-authentication every now and then.
Rolling: An extension of the "definite" version; the timespan gets renewed after each automatic login. The user doesn't have to re-enter their details unless they stop visiting for longer time. E.g. SE seems to use this approach; you don't have to re-authenticate if you visit every day, but have to after some weeks.
"Infinite": Once logged in, you never have to provide your credentials again for that device unless you're changing the authentication method or you log out. E.g. Facebook uses this approach (to be precise, they set the expiration to 2 years in the future, that's why I put quotes around "Infinite".
Each option has its pros and cons, e.g. 1) might be the best option as far as security is concerned and it also helps remembering your password if you have to provide it regularly. It has the obvious disadvantage, that the remember me expires even if you're visiting every day.
3) sounds like too much of a risk too me. But there seems to be a reason why Facebook doesn't provide an option to auto-logout yet...
The second option seems the best for me as regular users aren't forced to re-enter their password and still have the benefit of expiring if they forget to logout on e.g. a public device (although it could be too late even if expires in some hours).
Let's assume it's for an account that doesn't hold too much personal data and a missed "logout" couldn't do much harm:
What option would you as user prefer and why? Or is there a fourth option you're using (omitting the feature doesn't count)?
 Yes I know that it's always bad if a user account gets compromised, but there are less and more valuable user accounts. It's obvious why banking applications don't provide such an option, but there are websites where you rather take the risk than having to login each time.