For the most part, I find that time limits are always a detriment to games. The only one I can think of that I actually liked on some level was, as SakSak had brought up, the last mission in Halo.
However, the time limit in games generates one of three feelings in me: 1)I could play through this again if I really had to, though I'd much rather not. 2) I am never playing this mission again under any circumstances. 3) I am never playing this game again.
The first I felt in the end of Viewtiful Joe and in Resident Evil 4. The second I felt in that horrible timed mission at the end of Call of Duty 4. The third applies to Dead Rising, which I traded for the Fifth Anniversary Devil May Cry boxset. Though I already had DMC1 and DMC3, and I had played through DMC2, I still felt that I made a very good trade, as it gave me the opportunity to play as Vergil, ergo Nelo Angelo. I was definitely on the better end of that trade, because Dead Rising was really pathetic. The timed mission in the basement was merely the straw that broke my will to continue playing it.