If you’re doing the right amount of testing, there should be a good chance, whenever you kick off a test run, that your actions for the next hour will change depending on the results of the run. If you typically don’t change your actions based on the information from the tests, then the effort spent to write tests gathering that information was wasted.
David Heinemeier Hansson writes about sane amounts of software tests, which he ranted a bit about on Twitter today. I haven’t enough experience with tests yet, so I can’t give valid comments, but I’ve begun to understand what he and others are talking about thanks to a heavy focus on unit tests in a university course. Putting tons of time on writing tests instead of actual code seem insane to me, and David’s thoughts about having advice on what not to test seems legit to me (I recommend reading the comments on the Hacker News entry as well).
In the end I think it boils down to a healthy balance, as with everything else in life.