Half-Life 2 Episode 3/Half-Life 3.
It's been so long for me that at this point I don't care anymore. When and if it gets released, I'll surely end up playing it, but it won't be some big, "OMG OMG I HAZ TO HAZ IT NOW!!!!!" sort of moment. Same thing when it's other games that get announced and then never end up happening/take years to release as well. I think Diablo 3 was a prime example. I was super hyped for for Diablo 3 back in '03 or '04 before Blizzard/Vivendi/whoever shut Blizzard North down. Then when it came out all these years later, I just didn't care.
I'll bring up Rainbow Six: Patriots. I assume at this point that Ubisoft has pulled another Splinter Cell: Conviction on it and have started from scratch to create an entirely different Rainbow Six: Patriots, which wouldn't be too surprising to me considering the plot the game was supposed to have had the potential of being too controversial or just plain bad, the latter of which why Splinter Cell: Conviction disappeared for a while. The fact that both the narrative director and the lead designer were replaced definitely suggests that the entire story of the game has been overhauled/replaced in favor of something else other than the "True Patriots" thing and Wall Street.
Some that are particularly painful vaporware are Star Wars: Battlefront III and Timesplitters 4.