My coworker has that XKCD comic on the wall. He spends so much time trying to figure out what is worth his time to automate that all potential time savings are negated.
Two other effects which are often large:
First: suppose a task takes an hour and I do it once a month. If I can automate it so that it takes a few seconds, I don't just save an hour a month, I also have the option of doing it much more frequently.
Second: once a task has been automated it can become a building block for other tasks which I previously wouldn't have considered.
It's also about: decreasing errors, allowing for stuff to happen when operators are not around, ensuring testability and reproducible behaviors, scaling up processes.